CODEWITHSUNDEEP
mysqlsql
Brinley
Brinley

Reputation: 721

Include NULL values in MySQL SELECT results

I have the following MySQL tables:

tbl_pet_owners:

+----+--------+----------+--------+--------------+
| id | name   | pet      | city   | date_adopted |
+----+--------+----------+--------+--------------+
|  1 | jane   | cat      | Boston | 2017-07-11   |
|  2 | jane   | dog      | Boston | 2017-07-11   |
|  3 | jane   | cat      | Boston | 2017-06-11   |
|  4 | jack   | cat      | Boston | 2016-07-11   |
|  5 | jim    | snake    | Boston | 2017-07-11   |
|  6 | jim    | goldfish | Boston | 2017-07-11   |
|  7 | joseph | cat      | NYC    | 2016-07-11   |
|  8 | sam    | cat      | NYC    | 2017-07-11   |
|  9 | drew   | dog      | NYC    | 2016-07-11   |
| 10 | jack   | frog     | Boston | 2017-07-19   |
+----+--------+----------+--------+--------------+

tbl_pet_types:

+----------+-------------+
| pet      | type        |
+----------+-------------+
| cat      | mammal      |
| dog      | mammal      |
| goldfish | fish        |
| goldfish | seacreature |
| snake    | reptile     |
+----------+-------------+

I have the following SELECT statement 

SELECT DISTINCT owners.name, owners.pet, owners.city,
group_concat(DISTINCT types.type separator ', ') AS type
FROM tbl_pet_owners owners
INNER JOIN tbl_pet_types types ON owners.pet = types.pet
WHERE owners.city = 'Boston' OR owners.city = 'NYC'
GROUP BY owners.name, owners.pet
ORDER BY owners.city

..which returns this result:

+--------+----------+--------+-------------------+
| name   | pet      | city   | type              |
+--------+----------+--------+-------------------+
| jack   | cat      | Boston | mammal            |
| jane   | cat      | Boston | mammal            |
| jane   | dog      | Boston | mammal            |
| jim    | goldfish | Boston | fish, seacreature |
| jim    | snake    | Boston | reptile           |
| drew   | dog      | NYC    | mammal            |
| joseph | cat      | NYC    | mammal            |
| sam    | cat      | NYC    | mammal            |
+--------+----------+--------+-------------------+

Unfortunately, jack's frog is omitted from the results because there is no entry for frog in tbl_pet_types. How can I edit my query to include jack's frog in the results (with type = NULL)?

Upvotes: 0

Views: 3510

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

I can suggest the following query, which is similar to what you had originally but with a few modifications.

SELECT
    owners.name,
    owners.pet,
    owners.city,
    GROUP_CONCAT(DISTINCT COALESCE(types.type, 'NA') separator ', ') AS type
FROM tbl_pet_owners owners
LEFT JOIN tbl_pet_types types
    ON owners.pet = types.pet
WHERE owners.city = 'Boston' OR owners.city = 'NYC'
GROUP BY owners.name, owners.pet, owners.city
ORDER BY owners.city

You don't need to use DISTINCT in your select clause, because GROUP BY will already achieve what you had in mind. The most major change was switching to a LEFT JOIN. This should prevent records from being dropped.

Upvotes: 1

Isaiah3015
Isaiah3015

Reputation: 491

Use a LEFT JOIN to the tbl_pet_types instead of an INNER JOIN. What an INNER JOIN does is make sure you only see the records on both tables that are matching. In this particular case, you want all of the tbl_pet_owners data regardless if it's matching tbl_pet.

SELECT DISTINCT owners.name, owners.pet, owners.city,
group_concat(DISTINCT types.type separator ', ') AS type
FROM tbl_pet_owners owners
LEFT JOIN tbl_pet_types types ON owners.pet = types.pet
WHERE owners.city = 'Boston' OR owners.city = 'NYC'
GROUP BY owners.name, owners.pet
ORDER BY owners.city

Upvotes: 1

Colin Harrison
Colin Harrison

Reputation: 194

You're using an INNER JOIN so since there isn't a match on both tables, that row won't show up. Try using a LEFT JOIN so that all values in the left (first) table will show up, regardless of if there is an answer on the right (second) table.

You can visualize this with this image enter image description here

Upvotes: 2

Related Questions