MySQL query to convert rows of multiple tables into columns

Question

I need to ask you about MySQL select query from 3 tables.

I have 3 similar tables:

table_1

menu_id | menu_name
      1 | Menu 1
      2 | Menu 2

table_2

item_id | menu_id | item_name
      1 | 1 | Project 1
      2 | 1 | Project 2
      3 | 2 | Project 3
      4 | 2 | Project 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 perform the following output SELECT:

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 wish to populate the columns of the query (p_EUR, p_USD, etc.) with rows from table_3. If there are no matching records, 0 or NULL is returned. Tables are joined via table_1.menu_id=table_2.menu_id and table_2.item_id=table_3.item_id.

Answer 1

You can use case when

SELECT
t2.item_id,
t2.item_name,
t2.menu_id,
t1.menu_name,
 MAX(CASE WHEN t3.currency_code = 'EUR' THEN t3.price END) AS p_EUR,
 MAX(CASE WHEN t3.currency_code = 'USD' THEN t3.price END) AS p_USD,
 MAX(CASE WHEN t3.currency_code = 'GBP' THEN t3.price END) AS p_GBP,
 MAX(CASE WHEN t3.currency_code = 'RON' THEN t3.price END) AS p_RON,
 MAX(CASE WHEN t3.currency_code = 'LEV' THEN t3.price END) AS p_LEV
FROM table_2 AS t2
JOIN table_1 AS t1 ON t2.menu_id = t1.menu_id
LEFT JOIN table_3 AS t3 ON t2.item_id = t3.item_id
GROUP BY t2.item_id, t2.item_name, t2.menu_id, t1.menu_name;

If you need to convert null to 0, you can use COALESCE as shown below COALESCE(MAX(CASE WHEN t3.currency_code = 'LEV' THEN t3.price END), 0) AS p_LEV