mysql - 加入两个表并从一个表中获取一组值

Question

我有两个表:一个，主 表显示星期几和那天的汤。餐 table 汤，列出了可能的汤。

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

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

要获得星期五汤(字符串而不是 id)，我可以使用这样的查询:

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

我得到这样的东西(输出)

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

如果星期五只提供一份汤，这很有效。

问题是周五将提供两种汤(土 bean 和豌 bean - 一系列汤)

我们需要另一张 table 吗？

已编辑

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

是否可以在单次查询中得到除了主表的其他字段(id、day、id_soup)之外的soups数组(potatoes、peas)？

构造此查询的最佳方法是什么？

编辑:

期望的输出(仅用于说明):

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

已编辑

问题:如果想要的输出是这样的:

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

Answer 1

是的，如果每天有多个汤，您将需要一张中间 table 。您可以在 SELECT 语句中使用 GROUP_CONCAT 将一天的所有汤组合在一起。

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