Is it possible to use a value from a result to union another result into the previous resultset?

Question

So let's say that I have a table, BMST. I use this query to find a result set:

SELECT PARENT,
       CHILD,
       LEVEL,
       QTY
FROM   BMST
WHERE  PARENT = '111'          

In the result set are PARENT part numbers, as well as CHILD part numbers such as:

PARENT | CHILD  | LEVEL | QTY 
-----------------------------
111    | 222    | 0     | 2
111    | 333    | 0     | 1
111    | 444    | 0     | 1

The table gives information on all the CHILD parts that are used to make the PARENT part, as well as the QUANTITY of CHILD parts used in each PARENT part. The LEVEL column has a value of '0' because part '111' is the origin part that we care about. We do not care if part '111' is a CHILD part to another larger PARENT part.

It is possible for CHILD parts to be PARENT parts as well if they are made up of smaller CHILD parts. For example, this query:

SELECT PARENT,
       CHILD,
       LEVEL,
       QTY
FROM   BMST
WHERE  PARENT = '222'

would return:

PARENT | CHILD  | LEVEL | QTY 
-----------------------------
222    | 555    | 1     | 1
222    | 666    | 1     | 1
222    | 777    | 1     | 1

The LEVEL value in this new table is '1' because the part '222' is a CHILD part of a LEVEL = '0' PARENT part.

Going even further, the CHILD parts of part '222' could have CHILD parts themselves, so that a similar query for part '777' would return:

PARENT | CHILD  | LEVEL | QTY 
-----------------------------
777    | 999    | 2     | 2   

My question is, would it be possible to create a query that would return the first result set, and then check all of the CHILD part values within that result set to see if those have any CHILD parts, and then checks the resulting CHILD parts for even more CHILD parts, etc. until there are no more CHILD parts, and then UNION those that do into the first result set so it looks like:

PARENT | CHILD  | LEVEL | QTY 
-----------------------------
111    | 222    | 0     | 2
222    | 555    | 1     | 1
222    | 777    | 1     | 1
777    | 999    | 2     | 2
222    | 888    | 1     | 1
111    | 333    | 0     | 1
111    | 444    | 0     | 1

The LEVEL value needs to increment for every step deeper that the query goes, and the end result set should show every single part that goes into the requested PARENT part.

Is there a way to do all of this in SQL? Or do I have to use VB6 or another program to iterate through the loops? Any and all feedback is appreciated.

Answer 1

Instead of a union you should look into a SELF JOIN. You can join a table to itself, thereby linking the parent & child IDs. It will require a clause to eliminate rows being joined with themselves. The child ID is essentially the primary key in your example, and the parent ID acts as sort of a foreign key.

Here is an example of this done using Microsoft Access, since it was handy:

Table BMST:

PARENT  CHILD   LEVEL   QTY
111 222 0   2
111 333 0   1
111 444 0   1
222 555 1   1
222 666 1   1
222 777 1   1
555 aaa 2   11
555 aab 2   12
aaa xxx 3   100
aab www 3   111
aaa UUU 3   121

Query:

SELECT c.PARENT, c.CHILD, c.[LEVEL], c.QTY
FROM BMST AS P, BMST AS C
WHERE P.child = C.parent
and P.parent <> C.parent
ORDER BY c.level;

Results:

PARENT  CHILD   LEVEL   QTY
222 777 1   1
222 666 1   1
222 555 1   1
555 aab 2   12
555 aaa 2   11
aaa UUU 3   121
aab www 3   111
aaa xxx 3   100

Note that I invented some additional records to demonstrate that this covers all levels of the hierarchy. The query is imperfect because the topmost parent is excluded (having no parent itself) which probably could be address by using an outer join.

Curiously, the results of this particular example query closely mimics the table itself, but that is before any other criteria is applied, such as which parent element you are really interested in.

This question can provide more information: Explanation of self-joins

Answer 2

To do what you want you will need something called recursion. Ofcourse, you can parse it line by line (with T-SQL, or with VB, or whatever language you are comfortable in), however, this problem (recursion) is very easy to solve with something called Common Table Expressions or CTE.

With a CTE you are able to union against your result, so a PARENT-CHILD relation where the CHILD can be a PARENT is solveable in this case.

I have created this script to show you how. First i populate some Temp tables, after that i am querying using the CTE

if object_id('tempdb..#BMST') is not null
begin
  drop table #BMST
end

create table #BMST (
  PARENT varchar(5)
, CHILD varchar(5)
, LEVEL varchar(5)
, QTY varchar(5)
)

insert into #BMST
          select    '111', '222', 0, 2
union all select    '111', '333', 0, 1
union all select    '111', '444', 0, 1

union all select    '222', '555', 1, 1
union all select    '222', '666', 1, 1
union all select    '222', '777', 1, 1

union all select    '777', '999', 2, 2

Blow is the CTE. A Common Table Expression always has to be the first statement, so a semicolon is used for that. After that a with xxx as () construction starts. The results is a fictional name and can be anything. (In this example i used a new colom SECONDLEVEL to show you the new level)

;with results as (
select  *
      , 0 as SECONDLEVEL
from    #BMST b
union all
select  b.*
      , r.SECONDLEVEL + 1 as SECONDLEVEL
from    #BMST b
        inner join results r
          on  r.CHILD = b.PARENT
          and b.LEVEL > r.LEVEL

)
select  *
from    results

As you can see, i am using an UNION ALL operator. The top part is querying the #temp table, and it the bottom part it is using that to join to results that are already fetched.

And that's it. You have recursion now.