MySQL query to convert rows of multiple tables into columns-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

MySQL query to convert rows of multiple tables into columns

P粉882357979 2023-08-13 16:02:22

464

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.

P粉882357979

reply all (1)

P粉4760461652023-08-14 10:06:04 1 floor

You can usecase 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 useCOALESCEas shown belowCOALESCE(MAX(CASE WHEN t3.currency_code = 'LEV' THEN t3.price END), 0) AS p_LEV