josei josei - 1 month ago 4
MySQL Question

Join two tables and get from one table a array of values

I have two tables: one, the main table displays the day of the week and the soup that day.
The table soups, has a list of possible soups.

+-----+------------+--------+
| id | day |id_soup |
+-----+------------+--------+
| 1 | Friday | 1 |
| 2 | Saturday | 1 |
| 3 | Sunday | 2 |
+-----+------------+--------+

+-----+------------+
| id | soup |
+-----+------------+
| 1 | potatoes |
| 2 | carrots |
| 3 | peas |
+-----+------------+


To get the Friday soup (string not id) I can use a query like this:

SELECT ma.id, ma.day, ma.id_soup, so.soup
FROM main ma, soup so
WHERE ma.id_soup = so.id
AND ma.id = 1


I get something like this (OUTPUT)

+-----+------------+---------+----------+
| id | day | id_soup | soup |
+-----+------------+---------+----------+
| 1 | Friday | 1 | potatoes |
+-----+------------+---------+----------+


This works well if Friday is only available a single soup.

The problem is that on Friday will be available two soups (potatoes and peas - an array of soups)

We need another table?

EDITED

+-----+---------+---------+
| id | id_day | id_soup |
+-----+---------+---------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 2 |
| 4 | 2 | 3 |
| 5 | 3 | 1 |
| 6 | 3 | 3 |
+-----+---------+---------+


It is possible to obtain in a single query, in addition to other fields of the main table (id, day, id_soup), the soups array (potatoes, peas)?

What is the best way to construct this query?

EDITED:

Desired output (only to illustrate):

+-----+------------+---------+------------------+
| id | day | id_soup | soup |
+-----+------------+---------+------------------+
| 1 | Friday | 1 | potatoes, peas |
+-----+------------+---------+------------------+


EDITED

Question: and if de desired output is like:

+-----+------------+-------------------+
| id | day | soup |
+-----+------------+---------+---------+
| 1 | Friday | potatoes, carrots |
+-----+------------+---------+---------+
| 2 | Saturday | carrots, peas |
+-----+------------+---------+---------+
| 3 | Sunday | potatoes, peas |
+-----+------------+---------+---------+

Answer

Yes, you will need an intermediate table if there are multiple soups per day. You can goup all the soups for a day using GROUP_CONCAT in the SELECT statement.

http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

SELECT ma.id, ma.day, GROUP_CONCAT(DISTINCT so.soup) as soup
FROM main ma
LEFT JOIN soupmain sm ON(ma.id = sm.id_main)
LEFT JOIN soup so ON(so.id = sm.id_soup)
WHERE ma.id = 1
GROUP BY ma.id
Comments