Question 276023 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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

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

enter image description here

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

enter image description here

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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search