How to join a polymorphic table with its child tables?

Question

I am sorry about not being able to articulate the title of the question or the description of this question better. However, I will give the schema, sample data and expected result. Please help me write a query for such a use case.

Schema of restaurants

id
name
item_type
item_id

Schema of foods

id 
name

Schema of food_items

id 
name
food_id

Sample data in restaurants

|---------------------|------------------|---------------------|------------------|
|      id             |     name         |      item_type      |     item_id      |
|---------------------|------------------|---------------------|------------------|
|       1             | Apple Crushers   |      food_items     |         1        |
|---------------------|------------------|---------------------|------------------|
|       2             |     Retro Cafe   |      foods          |         2        |
|---------------------|------------------|---------------------|------------------|
|       3             | Fruit Mania      |      foods          |         1        |
|---------------------|------------------|---------------------|------------------|
|       4             | Meat and Eat     |      NULL           |        NULL      |
|---------------------|------------------|---------------------|------------------|

Sample data in foods:

|---------------------|------------------|
|      id             |     Name         |  
|---------------------|------------------|
|       1             |     Fruits       |     
|---------------------|------------------|
|       2             |     Chocolates   | 
|---------------------|------------------|

Sample data in food_items

|---------------------|------------------|---------------------|
|      id             |     Name         |      food_id        | 
|---------------------|------------------|---------------------|
|       1             |     Apple        |        1            | 
|---------------------|------------------|---------------------|
|       2             |     Mango        |        1            | 
|---------------------|------------------|---------------------|

I need to write a query such that I get this as my result.

|---------------------|------------------|---------------------|------------------|---------------------|------------------|
|      r_id           |     r_name       |      food_id        |     food_name    |    food_item_id     | food_item_name   |
|---------------------|------------------|---------------------|------------------|---------------------|------------------|
|       1             | Apple Crushers   |      1              |         Fruit    |        1            |  Apple           |
|---------------------|------------------|---------------------|------------------|---------------------|------------------|
|       2             |     Retro Cafe   |      2              |      Chocolates  |        NULL         |     NULL         |
|---------------------|------------------|---------------------|------------------|---------------------|------------------|
|       3             | Fruit Mania      |      1              |       Fruit      |      NULL           |    NULL          |
|---------------------|------------------|---------------------|------------------|---------------------|------------------|
|       4             | Meat and Eat     |      NULL           |        NULL      |         NULL        |       NULL       |
|---------------------|------------------|---------------------|------------------|---------------------|------------------|

p.s: It will also be very helpful if someone could come up with an appropriate title and description for this problem. I am lost for words to describe this.

Answer 1

For your database, I would suggest another data model based on compound keys. This would guarantee data consistency and makes queries a tad simpler:

food_group
(
  food_group_no  integer       not null,
  name           varchar(100)  not null,
  primary key (food_group_no)
);

food
(
  food_group_no  integer       not null,
  food_no        integer       not null,
  name           varchar(100)  not null,
  primary key (food_group_no, food_no)
);

restaurant
(
  restaurant_no  integer       not null,
  name           varchar(100)  not null,
  food_group_no  integer       not null,
  food_no        integer       null,
  primary key (restaurant_id),
  foreign key (food_group_no) references food_group(food_group_no),
  foreign key (food_group_no, food_no) references food(food_group_no, food_no)
);

The query:

select
  r.restaurant_no,
  r.name as restaurant_name,
  fg.food_group_no,
  fg.name as food_group_name,
  f.food_id,
  f.name as food_name
from restaurants r
join food_group fg on fg.id = r.food_group_no
left join food f on f.food_group_no = r.food_group_no and f.food_no = r.food_no
order by r.id;

Answer 2

select r.id, r.name, foods.id, foods.name, food_items.id, food_items.name from restaurents as r 
left join food_items on r.item_type = 'food_items' and r.item_id = food_items.id
left join foods on (r.item_type = 'foods' and r.item_id = foods.id) or (r.item_type = 'foods' and r.item_id = food_items.food_id) 

This might be having some syntax issues related to table names, but it should work.

Answer 3

You must join the food table twice and use COALESCE:

select
  r.id,
  r.name,
  coalesce(f.id, fif.id) as food_id,
  coalesce(f.name, fif.name) as food_name,
  fi.id as food_item_id,
  fi.name as food_item_name
from restaurants r
left join foods f on f.id = r.item_id and r.item_type = 'foods'
left join food_items fi on fi.id = r.item_id and r.item_type = 'food_items'
left join foods fif on fif.id = fi.food_id
order by r.id;