End Item query

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:
image

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;
1 Like

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 :+1:

That is only going one level up. Not all the way to the top level. Plus its a component in more than one parent.

When i ran it returned the parent it was a component of, but not the parent of its parent and so on.

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.

CREATE TABLE #tmp
(
	low_level INT,
	Par_item_no VARCHAR(32),
	Comp_item_no VARCHAR(32)
);

INSERT INTO #tmp VALUES
(1,'78662','58531'),
(2,'58531','30333'),
(3,'30333','30333-103'),
(4,'30333','05050');

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;