--THIS IS NOT THE BEST THING TO DO! but I think it will work. Somewhat.--You probably should take Jeff's suggestion about looking at BOL--Create a Temp table to hold your heirarchy. Probably could be made into procedure.IF OBJECT_ID('TempDB..#BOM','u') IS NOT NULL DROP TABLE #BOMGOCREATE TABLE #BOM(ID INT IDENTITY(1,1),Field1 VARCHAR(100),Field2 VARCHAR(100),FIeld3 VARCHAR(100),Field4 VARCHAR(100),Field5 VARCHAR(100),Field6 VARCHAR(100),BOM_Level INT,PRIMARY KEY (ID)) --Insert the Parent Codes that are never children and set the BOM_Level to 1INSERT INTO #BOM (Field1,Field2,Field3,Field4,Field5,Field6,BOM_Level)SELECT Field1,Field2,Field3,Field4,Field5,Field6,1 FROM BOM WHERE Field2 IN ('P','M') AND SUBSTRING(Field6,1,3) = 'S/A' AND Field1 NOT IN (SELECT DISTINCT Field3 FROM BOM) --Insert the Parent Codes that are also children, and set the BOM_Level = 2 INSERT INTO #BOM (Field1,Field2,Field3,Field4,Field5,Field6,BOM_Level)SELECT b1.Field1,b1.Field2,b1.Field3,b1.Field4,b1.Field5,b1.Field6,2 FROM BOM b1, #BOM b2 WHERE b1.Field1 = b2.Field3 AND b1.Field3 NOT IN (SELECT DISTINCT Field1 FROM #BOM WHERE BOM_Level = 1) AND SUBSTRING(b1.Field6,1,3) = 'S/A' AND b1.Field2 IN ('P','M') --Level 3INSERT INTO #BOM (Field1,Field2,Field3,Field4,Field5,Field6,BOM_Level)SELECT b1.Field1,b1.Field2,b1.Field3,b1.Field4,b1.Field5,b1.Field6,3 FROM BOM b1, #BOM b2 WHERE b1.Field1 = b2.Field3 AND b1.Field3 NOT IN (SELECT DISTINCT Field3 FROM #BOM WHERE BOM_Level IN (1,2)) AND b1.Field1 NOT IN (SELECT DISTINCT Field1 FROM #BOM WHERE BOM_Level IN (1,2)) AND SUBSTRING(b1.Field6,1,3) = 'S/A' AND b1.Field2 IN ('P','M') --Level 4INSERT INTO #BOM (Field1,Field2,Field3,Field4,Field5,Field6,BOM_Level)SELECT b1.Field1,b1.Field2,b1.Field3,b1.Field4,b1.Field5,b1.Field6,4 FROM BOM b1, #BOM b2 WHERE b1.Field1 = b2.Field3 AND b1.Field3 NOT IN (SELECT DISTINCT Field3 FROM #BOM WHERE BOM_Level IN (1,2,3)) AND b1.Field1 NOT IN (SELECT DISTINCT Field1 FROM #BOM WHERE BOM_Level IN (1,2,3)) AND SUBSTRING(b1.Field6,1,3) = 'S/A' AND b1.Field2 IN ('P','M') --Level 5INSERT INTO #BOM (Field1,Field2,Field3,Field4,Field5,Field6,BOM_Level)SELECT b1.Field1,b1.Field2,b1.Field3,b1.Field4,b1.Field5,b1.Field6,5 FROM BOM b1, #BOM b2 WHERE b1.Field1 = b2.Field3 AND b1.Field3 NOT IN (SELECT DISTINCT Field3 FROM #BOM WHERE BOM_Level IN (1,2,3,4)) AND b1.Field1 NOT IN (SELECT DISTINCT Field1 FROM #BOM WHERE BOM_Level IN (1,2,3,4)) AND SUBSTRING(b1.Field6,1,3) = 'S/A' AND b1.Field2 IN ('P','M') --Level 6INSERT INTO #BOM (Field1,Field2,Field3,Field4,Field5,Field6,BOM_Level)SELECT b1.Field1,b1.Field2,b1.Field3,b1.Field4,b1.Field5,b1.Field6,6 FROM BOM b1, #BOM b2 WHERE b1.Field1 = b2.Field3 AND b1.Field3 NOT IN (SELECT DISTINCT Field3 FROM #BOM WHERE BOM_Level IN (1,2,3,4,5)) AND b1.Field1 NOT IN (SELECT DISTINCT Field1 FROM #BOM WHERE BOM_Level IN (1,2,3,4,5)) AND SUBSTRING(b1.Field6,1,3) = 'S/A' AND b1.Field2 IN ('P','M') --Level 7INSERT INTO #BOM (Field1,Field2,Field3,Field4,Field5,Field6,BOM_Level)SELECT b1.Field1,b1.Field2,b1.Field3,b1.Field4,b1.Field5,b1.Field6,7 FROM BOM b1, #BOM b2 WHERE b1.Field1 = b2.Field3 AND b1.Field3 NOT IN (SELECT DISTINCT Field3 FROM #BOM WHERE BOM_Level IN (1,2,3,4,5,6)) AND b1.Field1 NOT IN (SELECT DISTINCT Field1 FROM #BOM WHERE BOM_Level IN (1,2,3,4,5,6)) AND SUBSTRING(b1.Field6,1,3) = 'S/A' AND b1.Field2 IN ('P','M') --Level 8INSERT INTO #BOM (Field1,Field2,Field3,Field4,Field5,Field6,BOM_Level)SELECT b1.Field1,b1.Field2,b1.Field3,b1.Field4,b1.Field5,b1.Field6,8 FROM BOM b1, #BOM b2 WHERE b1.Field1 = b2.Field3 AND b1.Field3 NOT IN (SELECT DISTINCT Field3 FROM #BOM WHERE BOM_Level IN (1,2,3,4,5,6,7)) AND b1.Field1 NOT IN (SELECT DISTINCT Field1 FROM #BOM WHERE BOM_Level IN (1,2,3,4,5,6,7)) AND SUBSTRING(b1.Field6,1,3) = 'S/A' AND b1.Field2 IN ('P','M') --Level 9INSERT INTO #BOM (Field1,Field2,Field3,Field4,Field5,Field6,BOM_Level)SELECT b1.Field1,b1.Field2,b1.Field3,b1.Field4,b1.Field5,b1.Field6,9 FROM BOM b1, #BOM b2 WHERE b1.Field1 = b2.Field3 AND b1.Field3 NOT IN (SELECT DISTINCT Field3 FROM #BOM WHERE BOM_Level IN (1,2,3,4,5,6,7,8)) AND b1.Field1 NOT IN (SELECT DISTINCT Field1 FROM #BOM WHERE BOM_Level IN (1,2,3,4,5,6,7,8)) AND SUBSTRING(b1.Field6,1,3) = 'S/A' AND b1.Field2 IN ('P','M') --Level 10INSERT INTO #BOM (Field1,Field2,Field3,Field4,Field5,Field6,BOM_Level)SELECT b1.Field1,b1.Field2,b1.Field3,b1.Field4,b1.Field5,b1.Field6,10 FROM BOM b1, #BOM b2 WHERE b1.Field1 = b2.Field3 AND b1.Field3 NOT IN (SELECT DISTINCT Field3 FROM #BOM WHERE BOM_Level IN (1,2,3,4,5,6,7,8,9)) AND b1.Field1 NOT IN (SELECT DISTINCT Field1 FROM #BOM WHERE BOM_Level IN (1,2,3,4,5,6,7,8,9)) AND SUBSTRING(b1.Field6,1,3) = 'S/A' AND b1.Field2 IN ('P','M')--You can see that this is ridiculously redundant, and you would have to hard code--for every level of nesting as I did for 1 through 10. As I said, this probably--could be made into a procedure (like the one in BOL), but that is beyond my --ability right now.--The below will only work to return the values you specified in your post example .xls--Like the above you will have to hard code every level above 2, but since I only needed--2 for your example thats all I did.--Good luck, and I hope you find a better way.DECLARE @ParentCode VARCHAR(100)SELECT @ParentCode = 'TX5922.AA' SELECT * FROM #BOM WHERE BOM_Level = 1 AND Field1 = @ParentCode AND Field2 IN ('M','P') AND SUBSTRING(Field6,1,10) = 'S/A OF PCB'UNIONSELECT b1.* FROM #BOM b1, #BOM b2 WHERE b2.Field3=b1.Field1 AND b1.BOM_Level = 2 AND SUBSTRING(b1.Field6,1,10) = 'S/A OF PCB' AND b1.Field1 NOT IN ( SELECT Field1 FROM #BOM WHERE BOM_Level = 1 AND Field1 = @ParentCode AND Field2 IN ('M','P') AND SUBSTRING(Field6,1,3) = 'S/A')