Having trouble self joining a table with NULL values-CodePudding

I have a single table that has the columns: personID, name, parentID1 and parentID2 Essentially I am looking to use this table to find a childs parent(s) Not every child has both parents, so these values are NULL in the table. I have tried using self joins but it just is not working out for me.

TABLE

Here is what the table should look like:

CodePudding user response：

Need to use LEFT joins so each row is returned even if the there is no match in the right table

List Parents for Each Person Even if 1 Parent is NULL

SELECT A.[name] AS Child
    ,B.[name] AS Parent1
    ,C.[name] AS Parent2
FROM YourTable AS A
LEFT JOIN YourTable AS B
    ON A.ParentID1 = B.PersonID
LEFT JOIN YourTable AS C
    ON A.ParentID2 = C.PersonID
WHERE A.ParentID1 IS NOT NULL
OR A.ParentID2 IS NOT NULL