CODEWITHSUNDEEP
mysqlsqlselectjoinnull
Lansana Camara
Lansana Camara

Reputation: 9871

MySQL: JOIN query that gets all items with a nullable field

I have an item table, which has a foreign key reference to a tax table. The column is tax_id.

I used to have the tax_id as NOT NULL in my schema, however I just changed this because I want it to be optional, so now it is NULL DEFAULT NULL in the schema.

This has broken my query. Now when I query for items and join the taxes, I only get the ones where the tax_id field matches.

This is my query:

SELECT item.*
FROM item
JOIN user_item
    ON user_item.item_id = item.id
JOIN tax
    ON tax.id = item.tax_id
WHERE user_item.user_id = ?
    AND item.deleted_at IS NULL
    AND user_item.deleted_at IS NULL
    AND tax.deleted_at IS NULL

This query works, however if I am given the items below:

+----+--------+--------------------+-------------+-------+---------------------+------------+------------+
| id | tax_id | name               | description | price | created_at          | updated_at | deleted_at |
+----+--------+--------------------+-------------+-------+---------------------+------------+------------+
|  1 |      2 | foo                |             |  1.13 | 2017-07-30 15:20:14 | NULL       | NULL       |
|  2 |   NULL | bar                |             |  0.67 | 2017-07-30 15:20:25 | NULL       | NULL       |
|  3 |   NULL | baz                |             |  1.15 | 2017-07-30 15:22:33 | NULL       | NULL       |
+----+--------+--------------------+-------------+-------+---------------------+------------+------------+

Then the query only returns the item with id 1. I want id 2 and 3 as well.

How can I modify my query to achieve this?

I understand the problem of the query is this part: ON tax.id = item.tax_id.

Upvotes: 1

Views: 216

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I think the best approach is to put the deleted_at condition in the on clause. If you want NULL values to match between the tables, then write this as:

SELECT i.*
FROM item i JOIN
     user_item ui
     ON ui.item_id = i.id LEFT JOIN
     tax t
     ON NOT (t.id <=> i.tax_id) AND  -- ids are the same or both `NULL`.
        t.deleted_at IS NULL
WHERE ui.user_id = ? AND
      i.deleted_at IS NULL AND
      ui.deleted_at IS NULL ;

Upvotes: 1

Mureinik
Mureinik

Reputation: 312259

This is exactly what left joins are for:

SELECT item.*
FROM item
JOIN user_item
    ON user_item.item_id = item.id
LEFT JOIN tax -- Here!
    ON tax.id = item.tax_id
WHERE user_item.user_id = ?
    AND item.deleted_at IS NULL
    AND user_item.deleted_at IS NULL
    AND tax.deleted_at IS NULL

Upvotes: 2

Related Questions