CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expression
zango123
zango123

Reputation: 1658

cte to get family tree in many to many relationship

i have a table named parents and another of parents_rel. its a many to many relationship where each parent has children and the children have children as well. i want to use a cte to get the family tree of a given parent. here is my dataset:

Parents

id name
1 bob
2 rick
3 ann
4 charles
5 carlos
6 maria

Parents_rel

Parent_id Child_id
1 2
1 3
2 4
5 6

If i want to see the family tree of bob(1), my expected out come is:

id name
1 bob
2 rick
3 ann
4 charles

im using postgres, so examples in postres would be much appreciated.

Upvotes: 0

Views: 344

Answers (2)

zango123
zango123

Reputation: 1658

This is what i ended up doing:

WITH RECURSIVE n
AS (
  SELECT p.* 
  FROM parents p  
  WHERE p.id  = "bob"
UNION ALL
  SELECT p1.* 
  FROM (SELECT child_id FROM parents_rel pr INNER JOIN n ON pr.parent_id 
   = n.id) a
INNER JOIN parents p1
 ON a.child_id = p1.id
)
SELECT * FROM n;

Upvotes: 0

Random User
Random User

Reputation: 341

This should work :

WITH RECURSIVE CTE AS(
SELECT id ParentId
  FROM Parents Parent
  WHERE Parent.Name = 'bob'
UNION ALL
SELECT Child_id
  FROM Parents_rel
  JOIN CTE ON Parents_rel.Parent_id = CTE.ParentId
)
SELECT ParentId id, name
FROM CTE
LEFT JOIN Parents ON Parents.Id = CTE.ParentId

Try it online

Upvotes: 1

Related Questions