Using Result Of SQL Statement

Hi,

What I would like to do is run a select statement, and then have the result of the select be used/run thru the same select again untill it hits a certain where clause.

This is for a where used search in our BOM (Bill of Material) table.

So for example

BOM 1

level 1 Item# 123 type Finished Good
level 2 Item# 45 type subassembly
level 3 Item# 56 type purchased item


BOM 2

level 1 Item# 678 type Finished Good
level 2 Item# 78 type subassembly
level 3 Item#56 type purchased item


So my input into the select statement would be Item# 56, it will then first find Item# 45 and Item# 78, but those I am not interested in. I want the select to then take Item#45 and Item#78 and run it thru the same select so that it will find Item#123 and Item#678.

The select will know to stop there since these are flagged as "Finished Good" in our system, so they will not be in any other BOM. This is what I would imagine being specified in the "where clause".

I can write the first part, where the select will find Item #45 and Item#78, but don't know how to get past that point.

Any help or suggestions are greatly appreciated!!

Thanks,
Michiel