SQL QUERY - Provide a student/parent list. Formatted child name, parent name 1, parent name 2. If a child doesn’t have a parent, just display null

Question

I am working on an SQL problem for a class - "Provide a student/parent list. Formatted child name, parent name 1, parent name 2. If a child doesn’t have a parent, just display null."

The expected result should look like this

However, I have been unable to get the result to return "NULL" values, I am stuck only getting rows returned where there is no null

Utilizing this code

SELECT child.name AS Child, parent1.name AS Parent1, parent2.name AS Parent2
FROM Person child 
JOIN Person parent1 ON child.parentID1 = parent1.personID 
JOIN Person parent2 ON child.parentID2 = parent2.personID 
ORDER BY child.name; 

Any help in how to modify the SQL to include null values would be greatly appreciated

Answer 1

You can use a LEFT JOIN for each parent, which will ensure that even if a child doesn't have a parent, they will still be included in the result with "NULL" values.

enter image description here

The modified query should be something like this:

SELECT child.name AS Child, parent1.name AS Parent1, parent2.name AS Parent2
FROM Person child 
LEFT JOIN Person parent1 ON child.parentID1 = parent1.personID 
LEFT JOIN Person parent2 ON child.parentID2 = parent2.personID 
ORDER BY child.name;

A similar template for the SQL Query:

SELECT
    s.child_name,
    p1.parent_name AS parent_name_1,
    p2.parent_name AS parent_name_2
FROM
    Students AS s
LEFT JOIN
    Parents AS p1 ON s.child_id = p1.child_id AND p1.parent_number = 1
LEFT JOIN
    Parents AS p2 ON s.child_id = p2.child_id AND p2.parent_number = 2;

You can read more about LEFT JOIN

• LEFT JOIN vs. LEFT OUTER JOIN in SQL Server
• What is the difference between "INNER JOIN" and "OUTER JOIN"?
• What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

Answer 2

You are using JOIN and it is short form of INNER JOIN ( JOIN = INNER JOIN). INNER JOIN is just matching values in a field common to both tables and it will not return null values (In the cases there is no existed ID parent, it will not return NULL). Hence, we need to use LEFT JOIN in this case. It will keep all records of childs and return null value for parent column if there is no existing their parent ID. Hope it helps