MySQL query multiple tables and return fields as null when id not found

Question

I am very new to MySQL queries and I'm trying to do a large SQL query from multiple tables. I have three tables, customer, order, and food. They are similar to the following:

customer
id  |  order_id  | purchase_category
----+------------+------------------
1   |  123       |  'Sandwich'
2   |  456       |  'Item'
3   |  789       |  'Dessert'
4   |  NULL      |  'Item'


order
order_id  |  payment_method
---------------------------
123       |  'credit_card'
456       |  'debit_card'

food
id  |  type
-----------
1   |  'Burger'
3   |  'Cake'

item
id  |  product
--------------
2   |  'Stickers'
4   |  'Game'

I am trying to find the fields by the id. My desired output from a SQL query is this, if looking for id 1:

order_id  |  purchase_category  |  payment_method  |  type    |  product
----------------------------------------------------------------------
123       |  'Sandwich'         |  'credit_card'   | 'Burger' |  NULL

And if looking for id 2:

order_id  |  purchase_category  |  payment_method  |  type    |  product
----------------------------------------------------------------------
456       |  'Item'             |  'debit_card     | NULL     |  'Stickers'

And if looking for id 3:

order_id  |  purchase_category  |  payment_method  |  type    |  product
----------------------------------------------------------------------
789       |  'Dessert'          |  NULL            | 'Cake'   |  NULL

And if looking for id 4:

order_id  |  purchase_category  |  payment_method  |  type    |  product
----------------------------------------------------------------------
NULL      |  'Item'             |  NULL            |  NULL    |  'Game'

Notice how in the tables, an ID may not exist in the table, but I still want to return the desired fields as NULL if the ID does not exist in the table. I have researched extensively to try to find the solution and I can't seem to get it. Here is the code I have written so far:

SELECT customer.order_id, customer.purchase_category, order.payment_method, food.type, item.product
FROM customer, food, item
LEFT JOIN order
ON customer.order_id=order.order_id
WHERE customer.id=1 and food.id=1 and item.id=1

However when an id does not exist in one of the tables, the whole thing returns the empty set. I just need the desired fields to populate with NULL in this case. I also need to protect against when an order_id is NULL in the customer table, e.g. output when looking for id 4.

sheeju · Accepted Answer · 2015-09-28T15:43:53.903

1

SELECT customer.order_id, customer.purchase_category, 
order.payment_method, food.type, item.product 
FROM customer 
LEFT JOIN order ON customer.order_id = order.id 
LEFT JOIN food ON customer.id = food.id 
LEFT JOIN item ON customer.id = item.id

You should use LEFT JOIN as mentioned above.

+----------+-------------------+----------------+--------+----------+
| order_id | purchase_category | payment_method | type   | product  |
+----------+-------------------+----------------+--------+----------+
|      123 | Sandwich          | credit_card    | Burger | NULL     |
|      456 | Item              | debit_card     | NULL   | Stickers |
|      789 | Dessert           | NULL           | Cake   | NULL     |
+----------+-------------------+----------------+--------+----------+

3 rows in set (0.00 sec)

edited Sep 28 '15 at 15:43

answered Sep 28 '15 at 15:30

sheeju

56
4

I should have put this in the original question, but I also have to protect against whether the order_id is NULL. If the order_id is NULL in the customer table, I need to populate the payment_methods as NULL instead of the result just being the empty set for that id. – curtis Sep 28 '15 at 16:42
The same query should work and will return payment_method as NULL if order_id is NULL – sheeju Sep 29 '15 at 01:31

score 0 · Answer 2 · edited May 23 '17 at 11:44

First, you need to create a relationship between the customer table with food and item.

If it is a 1 to 1 relationship, in the customer table you can add food_type_id and item_type_id both field default to NULL.

Then the query can be modified like this

SELECT customer.order_id, customer.purchase_category, order.payment_method, food.type, item.product FROM customer LEFT JOIN food ON customer.food_type_id = food.id LEFT JOIN item ON customer.item_type_id = item.id INNER JOIN order ON customer.order_id=order.order_id WHERE customer.id=1 and food.id=1 and item.id=1

Join diagram here: sql joins as venn diagram Look at the relationship here: How to create relationships in MySQL

Please include essential code examples to the answer. Do not just link to other sites/articles — toesslab, Sep 28 '15 at 15:30

MySQL query multiple tables and return fields as null when id not found

2 Answers2