How to join to 2 different tables based on field value of main table?

user_4698790 0
Viewed 315

So I have 3 tables named menus, products and categories.

menus table has id, item_type which can be product|category and item_id.

What I'd like to do is when menu item_type = product, join it with the products table ON products.id = menu.item_id and join with categories when item_type = category.

This is my current query:

SELECT m.* FROM menus m 
LEFT JOIN products p ON p.id = m.item_id AND m.item_type = 'product' 
LEFT JOIN categories c ON c.id = m.item_id AND m.item_type = 'category'

The query works though I'm not seeing any product or category data attached to each menu.

Am I missing something here? Thanks.

join mysql sql
3 Answers

You need to SELECT the columns that you want. You are only selecting columns from the menus table, so that is all you get.

I would suggest selecting columns from the other two tables, but combining them using COALESCE():

SELECT m.*,
       COALESCE(p.name, c.name) as name,  -- or whatever the columns are
       -- repeat for additional columns that you want
FROM menus m LEFT JOIN
     products p
     ON p.id = m.item_id AND m.item_type = 'product' LEFT JOIN
     categories c
     ON c.id = m.item_id AND m.item_type = 'category'
user_11440350

You are selecting only the values in menu by using a SELECT m.*

The correct query is to just use * or the fields you require specifically.

SELECT * FROM menus m 
LEFT JOIN products p ON p.id = m.item_id AND m.item_type = 'product' 
LEFT JOIN categories c ON c.id = m.item_id AND m.item_type = 'category'
user_79188090

You need to include the fields of the tables to JOIN with as well, since you're only selecting the fields of the m table.

Eventually you might also need to make your joins OUTER to still include records that don't match to any of your 2 joins: e.g.: menus with another item_type (which is not your actual use case, as I can see now, btw)

SELECT m.*, p.*, c.* FROM menus m 
LEFT JOIN products p ON p.id = m.item_id AND m.item_type = 'product' 
LEFT JOIN categories c ON c.id = m.item_id AND m.item_type = 'category'

Should be sufficient enough to return all of your menu records, some of them with only product info (NULL values for category fields) and viceversa:

  • p.* values with NULLs when item_type=category
  • c.* values with NULLs when item_type=menu
user_116939240
Related