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

SQL : 3 table joins with NULL values – Mysql

Ahh…been banging my head against this because I cant seem to figure it out.

My tables:

$ select * from my_family_members;
+-----------+-----------+-----------------------------+
| member_id | house_id  | member_name                 |
+-----------+-----------+-----------------------------+
|  2        |         2 | Ash                         |
|  5        |         5 | Mel                         |
|  8        |         2 | NULL                        |
+-----------+-----------+-----------------------------+

$ select * from my_houses;
+------------------------------------------+
| house_id | code     | house_name         |
+------------------------------------------+
|  2       | house1   | My Blue house      |
|  5       | house2   | My Red house       |
|  8       | house3   | My Green house     |
+------------------------------------------+


$ select * from my_pets;
+-----------+--------------+----------------+
| member_id | config_key   | config_value   |
+-----------+--------------+----------------+
| 2         | cat          | Tom            |
| 2         | cat          | Jerry          |
| 5         | dog          | Buster         |
+-----------+--------------+----------------+

I’m trying to do a query that will get me house_id, member_id and config_value of their cats including NULL values. ie:

+---------------+------------------------------+
| house_name    | member_name  |  cats         |
+---------------+------------------------------+
| My Blue House | Ash          |  Jerry        |
| My Blue House | Ash          |  Tom          |
| My Red House  | Mel          |  NULL         |
| My Green House| NULL         |  NULL         |
+---------------+------------------------------+

I tried a query like:


select mh.house_name, mfm.member_name, config_value as cats
from my_pets mp
LEFT OUTER JOIN my_houses mh
ON mp.house_id=mh.house_id 
JOIN my_family_members mfm
ON mfm.house_id=mh.house_id
where config_key = 'cat' order by house_name;

But I can only get:

+---------------+------------------------------+
| house_name    | member_name  |  cats         |
+---------------+------------------------------+
| My Blue House | Ash          |  Jerry        |
| My Blue House | Ash          |  Tom          |
+---------------+------------------------------+

What do I need to change to get the result I want?

EDIT: The logic is that there is always a house, but the house can have a cat without actually having anyone living in it so member_name is NULL and cat is not NULL (poor cat..), there can also be a house with a member with no cat (cat is NULL, but member is not NULL), and lastly there can also be a house with no one in it and no cats (cat is NULL, member_name is NULL, although there might be a dog but I only want to see if there is a cat or people in the house).

Thanks!

Tags:

2

Answers


  1. 0

    you seem to be approaching the problem from the wrong direction. You want all houses so start with that table (my_houses). You then want people who live in each house – without excluding houses with no people – so left join people (my_family_members) to houses. You then want any pets that exist – without excluding houses/people – so left join pets (my_pets) to people.

    So something like:

    SELECT ...
FROM my_houses h
LEFT OUTER JOIN my_family_members fm ON h.house_id = fm.house_id
LEFT OUTER JOIN my_pets p ON fm.member_id = p.member_id
    Login or Signup to reply.
  2. 0

    Join my_houses to the other tables with LEFT joins like this:

    SELECT h.house_name, 
       fm.member_name, 
       p.config_value cats
FROM my_houses h
LEFT JOIN (SELECT * FROM my_family_members WHERE member_name IS NOT NULL) fm
ON fm.house_id = h.house_id  
LEFT JOIN my_pets p 
ON p.member_id = fm.member_id AND p.config_key = 'cat'
ORDER BY h.house_name, p.config_value;

    See the demo.

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

Back To Top
Search