0

I have a table that has child_id and parent_id. The child_id has the leaf node and the parent_id has the immediate parent. I need to get all the parents and all their children using a SQL query

Could someone help me with this?

I tried a recursive CTE to get all the children of a particular parent and I have a query for this, but now I need to get all the parents and all their children in a table.

WITH CTE as (
  SELECT child_id,parent_id 
  FROM Hierarchy 
  where parent_id ='Africa'

  UNION ALL

  SELECT Hierarchy.child_id,Hierarchy.parent_id 
  FROM Hierarchy 
    inner join CTE on Hierarchy.parent_id=cte.child_id
  where Hierarchy.parent_id !='Africa'
)
SELECT * 
FROM CTE
OPTION (MAXRECURSION 0)

With the above query, I just get Africa's children. I want all the children of all the continents

Shravan Vijayaprasad
  • 23
  • 1
  • 6
  • You don't need `where Hierarchy.parent_id !='Africa'` remove it –  Jul 23 '19 at 14:36
  • Do you know the max depth of the hierarchy? Or need to blindly bypass all the hierarchy? – Llex Jul 23 '19 at 15:06

1 Answers1

-1

You don't need a CTE for this, I think. Assuming the child_id is the primary key (or at least some kind of unique identifier), a simple self-join will do:

SELECT
   p.parent_id,
   c.child_id
FROM
   Hierarchy p
   INNER JOIN Hierarchy c ON c.parent_id = p.child_id
Michael Tobisch
  • 1,034
  • 6
  • 15
  • 2
    In case of your join, we couldn't know the child of child, for example:) – Llex Jul 23 '19 at 15:34
  • Sorry, in this case I did not understand your question exactly. Could you please show some example rows from the table, and the expected query result? – Michael Tobisch Jul 24 '19 at 07:30
  • It's not my question, but i think the purpose is to get all hierarchy, not only relation parent-child. For example, we want to new, that we have 5 roots(parents, that haven't got parents), then their children(those rows, where ParentID = RootID), then next level of children(those rows, where ParentID= child of root parent%) ) and so on. The purpose is to recursively check all the hierarchy. Sorry, it is difficult to explain, but it is just my interpretation of question, may be the author meant another. – Llex Jul 24 '19 at 08:51
  • 1
    Maybe [this](https://stackoverflow.com/questions/57176427/sql-get-parent-tree-hierarchy-from-child) is helpful... It displays the hierarchy the other way round (from the leaf to the root). But a unique root is necessary, even if it is a virtual one. – Michael Tobisch Jul 24 '19 at 08:55