Join two tables and get from one table a array of values
join, mysql, sql
Solution
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
Problem
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 | +-----+------------+---------+---------+ ```