I'm working with a Bill of Material table. Which holds Parent_item, comp_item and Qty_Per
Table Name: bmprdstr
I'm looking to create a query there I enter a comp_item and it returns the top level end item that it belongs to.
Example of the data:
In the pic above if I enter comp_item_no 05050 I would want it to return parent item 78622 since it is the top level 1 item. Bills of Material can be up to 11 levels deep.
DECLARE @CompItem VARCHAR(32) = '05050';
;WITH cte AS
(
SELECT Comp_Item_No, Par_item_no, 0 AS Lvl
FROM
YourTable
WHERE
Comp_Item_no = @CompItem
UNION ALL
SELECT
y.Comp_Item_No, y.Par_item_no, Lvl+1
FROM
cte c
INNER JOIN YourTable y ON
y.Comp_Item_no = c.Par_item_no
)
SELECT TOP (1) Par_Item_No FROM cte ORDER BY Lvl DESC;
You picture looks like a result of a query - not a table, and the reason I'm guessing at this is because, you have duplicates (3 rows of par_item_no=78622 and comp_item_no=NOTES).
The solution from @JamesK is exactly what I would have proposed
It is hard to figure out the structure of the data and to write a query and test it with just a screenshot. What you should do is post representative sample data in a manner that someone can copy and past to their query window and then be able to write a query against that.
What I mean is something like shown below. This is not the complete data set you posted - I am just showing how you would want to post your sample input data.
Once you have done that, most of the regulars on this forum would be able to suggest accurate solutions. More often than not, the difficult part is understanding your data and creating sample data to write a query against. Once you help in doing that by posting DDL as I have shown below, the rest is usually easy.
The query I posted early does traverse up the tree and give you the result you are looking for based on the sample data I created.
DECLARE @CompItem VARCHAR(32) = '05050';
;WITH cte AS
(
SELECT Comp_Item_No, Par_item_no, 0 AS Lvl
FROM
#tmp
WHERE
Comp_Item_no = @CompItem
UNION ALL
SELECT
y.Comp_Item_No, y.Par_item_no, Lvl+1
FROM
cte c
INNER JOIN #tmp y ON
y.Comp_Item_no = c.Par_item_no
)
SELECT TOP (1) Par_Item_No FROM cte ORDER BY Lvl DESC;