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.
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.