MySQL连接语句 [英] MySQL join statement

问题描述

有人可以提供一些帮助吗?！...

Could anyone offer some help please?!...

我有一个名为饭菜的sql数据库表，另一个有一个名为menu的数据库表:

I have an sql database table called meals and another database table called menu:

meals (id, name, description, cost)
menu (date, option1, option2)

这个想法是，在一天中有2种餐点可供选择，例如:

The idea being that there are 2 meal choices in a given day, for example:

09/02/16  meal id 1 OR meal id 8
10/02/16  meal id 4 OR meal id 12

我想使用类似于以下内容的连接语句:

I want to use a join statement similar to this:

select * from meals JOIN menu ON meals.id = menu.option1 AND meals.id=menu.option2

但是每次我运行此命令时，我都会收到一条错误消息，说表别名不是唯一的.

but every time I run this I get an error message, saying table alias not unique.

我希望显示最终输出:

date: option 1 meal id, name, cost AND option 2 meal id, name, cost 

例如

09/02/16 - 1(id) Chicken curry £2.50 + 8 (id) Roast Beef £3.00
10/02/19 4 (id) Baked potato £2.50 + 12 (id) Soup/Sandwiches £2.75

推荐答案

使用OR而不是AND，因为您要在单顿饭中匹配任何一个选项，而不是两个都匹配.也可以使用IN.

Use OR rather than AND, because you want to match either option, not both in a single meal. This can also be done using IN.

SELECT *
FROM meals
JOIN menu ON meals.id IN (menu.option1, menu.option2)

如果要在同一行中同时获取两餐，请使用GROUP_CONCAT

If you want to get both meals for a date in the same row, use GROUP_CONCAT

SELECT menu.date, GROUP_CONCAT(' + ', CONCAT_WS(' ', meals.id, meals.name, meals.cost)) AS options
FROM meals
JOIN menu ON meals.id IN (menu.option1, menu.option2)
GROUP BY menu.date

这篇关于MySQL连接语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！