Andy. I was messing around with your spreadsheet today and this is what I came up with in my attempt to understand what the BOL procedure is doing. I would not use this if I were you, but it might help you see what you are asking of your tables.

--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 #BOM

GO

CREATE 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 1

INSERT 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 3

INSERT 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 4

INSERT 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 5

INSERT 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 6

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

INSERT 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 8

INSERT 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 9

INSERT 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 10

INSERT 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'

UNION

SELECT

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'

)