-1

I need to ask you for help with MySQL select query from 3 tables.

I have 3 tables like that:

table_1

menu_id | menu_name
      1 | Menu 1
      2 | Menu 2

table_2

item_id | menu_id | item_name
      1 |       1 | Item 1
      2 |       1 | Item 2
      3 |       2 | Item 3
      4 |       2 | Item 4

table_3

price_id | item_id | currency_code | price
       1 |       1 |           EUR | 3.65
       2 |       1 |           USD | 3.45
       3 |       2 |           EUR | 9.00
       4 |       4 |           LEV | 4.85
       5 |       4 |           RON | 9.60

I need to make an output SELECT like:

item_id | item_name | menu_id | menu_name | p_EUR | p_USD | p_GBP | p_RON | p_LEV
      1 | Item 1    |       1 | Menu 1    | 3.65  | 3.45  | 0     | 0     | 0
      2 | Item 2    |       1 | Menu 1    | 9.00  | 0     | 0     | 0     | 0
      3 | Item 3    |       2 | Menu 2    | 0     | 0     | 0     | 0     | 0
      4 | Item 4    |       2 | Menu 2    | 0     | 0     | 0     | 9.60  | 4.85

I want to populate query columns (p_EUR, p_USD, etc.) with rows from table_3. If no records match, return 0 or NULL. Tables joined with table_1.menu_id=table_2.menu_id and table_2.item_id=table_3.item_id

Ciprian
  • 21
  • 6
  • You can ***probably*** do away with the surrogate PK on `table_3` and create `PK(item_id, currency_code)`, as there should be a composite key on these two columns anyway. – user1191247 Aug 12 '23 at 13:53

0 Answers0