Find all ancestors without direct id-parentid in same table-CodePudding

I have a parent-child structure across two tables. The first table has BOM_ID for bills and ITEM_ID for associated children items. The other has BOM_ID and ITEM_ID for the parent item.

I am able to find the first level of parents' ITEM_ID with the following query

SELECT item_id
FROM bomversion
WHERE bom_id IN (SELECT bom_id FROM bom WHERE item_id = 1)

So, in order to find all ancestors, I would have to repeat this step. I've tried to look at CTE and recursion techniques but all examples have parentid and childid in the same table. I cannot figure this one out.

If 1 is child of 2, 2 is child of 3, 3 is child of 4, and 2 is also child of 5, I am looking for the following result:

ChildID	ParentID
1	2
2	3
2	5
3	4

The starting point will be a specific ChildID.

S O L U T I O N

Based on Adnan Sharif's proposal, I found the solution for my problem:

WITH items_CTE AS (
  -- create the mapping of items to consider
  SELECT
   B.ITEMID AS Child_id,
   BV.ITEMID AS Parent_Id
  FROM BOM AS B
  INNER JOIN BOMVERSION AS BV
  ON B.BOMID = BV.BOMID
), parent_child_cte AS (
  -- select those items as initial query
  SELECT
    Child_id,
    Parent_id
  FROM items_CTE
  WHERE Child_id = '111599' -- this is the starting point
  UNION ALL
  -- recursive approach to find all the ancestors
  SELECT
   c.Child_Id,
   c.Parent_Id
  FROM items_CTE c
  JOIN parent_child_cte pc
  ON pc.Parent_Id = c.Child_id
)
SELECT * FROM parent_child_cte

CodePudding user response：

From the dbfiddle that you shared in the comment, if I understand you correctly, you want to have the rows showing all the parents of a child. For example, lets consider the hierarchy, 1 is a child of 2, 2 is a child of 3, and 3 is a child of 4. You want the final result as,

child_id	parent_id
1	2
1	3
1	4
2	3
2	4
3	4

If that's the case, we can use recursive CTE to build that table. First of all, we need to be build a relation between those two tables based on bom_id and then we need to find out parent-child relation. After that we will add rows based on the initial query. Please see the below code.

WITH RECURSIVE items_CTE AS (
  -- create the mapping of items to consider
  SELECT
   B.Item_id AS Child_id,
   BV.Item_id AS Parent_Id
  FROM BOM AS B
  INNER JOIN BOMVERSION AS BV
  ON B.bom_id = BV.bom_id
), parent_child_cte AS (
  -- select those items as initial query
  SELECT
    Child_id,
    Parent_id
  FROM items_CTE
  
  UNION
  
  -- recursive approach to find all the ancestors
  SELECT
   parent_child_cte.Child_Id,
   items_CTE.Parent_Id
  FROM items_CTE
  INNER JOIN parent_child_cte
  ON parent_child_cte.Parent_Id = items_CTE.Child_id
)
SELECT * FROM parent_child_cte