SQL parent child recursive query - Includes siblings
I have the following table with two columns.
Parent Child
A C
A D
B A
B E
C H
J C
F G
G I
So, I need to pass parent A, and I need to get the following answer: all parents and children, but also all parents and children for those who are related to A (parent or children) I need all their parents and children, etc. ...
SO in the example of passing A passed to proc I get the following
A C
A D
B A
B E
C H
J C
If F passed I would just get
F G
G I
+1
source to share
2 answers
select * from test
where parent in (
select parent from test
where parent = 'F' or child = 'F'
union
select child from test
where child = 'F' or parent = 'F')
or child in (
select parent from test
where parent = 'F' or child = 'F'
union
select child from test
where child = 'F' or parent = 'F');
+1
source to share
Try this, it is similar to the link given in the comments but takes into account both parent and recursion. Hope this works for you.
WITH relationships AS (
SELECT *
FROM Table
WHERE Child = 'A'
UNION ALL
SELECT p.*
FROM Table p
JOIN relationships pa on pa.Child = p.Parent
UNION ALL
SELECT c.*
FROM Table c
JOIN relationships ch on ch.Parent = c.Child
)
select *
from name_tree;
+1
source to share