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

Question

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.

Answer 1

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'

Answer 2

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'