SQL Server query to find parent names of child
I have a table like this
ID Name Mother Father
1 Sue NULL NULL
2 Ed NULL NULL
3 Emma 1 2
4 Jack 1 2
5 Jane NULL NULL
6 Bonnie 5 4
7 Bill 5 4
and I need the output as below
ID Name Mother Father
3 Emma sue ed
4 jack sue ed
6 bonnie jane jack
7 bill jane jack
I tried writing a query with n cte appended but couldn't come up with the logic, can someone please help me
+3
source to share
3 answers
SELECT t.ID, t.Name, m.Name, f.Name
FROM your_table t
INNER JOIN your_table m ON m.ID = t.Mother
INNER JOIN your_table f ON f.ID = t.Father
Use LEFT JOIN
if you want to include entries without Mother
and / or Father
nodes:
SELECT t.ID, t.Name, ISNULL(m.Name, 'Orphan') Mother, ISNULL(f.Name, 'Orphan') Father
FROM your_table t
LEFT JOIN your_table m ON m.ID = t.Mother
LEFT JOIN your_table f ON f.ID = t.Father
+4
source to share