mysql - SQL JOIN - 不明白

Question

我不知道如何获得我需要的结果。有人可以帮忙吗？

这些是我的 mySql 表:

restaurant           menu                  menu_has_dishes             dishes
----------           ----                  ---------------             ------
id (k)        <->    restaurantid          id (k)                      title
name                 id (k)       <->      menuid                      price
                     date                  dishid             <->      id (k)

我需要获取属于餐厅的某一天的所有菜单及其各自的菜肴列表。谁能告诉我正确的 SQL 查询并解释一下...？

谢谢...:)

Answer 1

SELECT d.title, d.price
FROM dishes d
JOIN menu_has_dishes md ON d.id = md.dishid
JOIN menu m ON md.menu_id = m.id
WHERE m.restaurantid = [[your restaurant id goes here]]

这会给你这样的输出:

-----------
|Fish|1.00|
|Ham |2.00|
|... |... |
-----------

所以让我们分解这个查询的各个部分:

--Tell the database what fields you care about
SELECT d.title, d.price

--Tell it what table to start with, and give it a nickname "d"
FROM dishes d

--join to menu_has_dishes so we know what menus contain each dish
JOIN menu_has_dishes md ON d.id = md.dishid

--join to menu so we know what restaurant has each menu
JOIN menu m ON md.menu_id = m.id

--finally, tell it what restaurant to look at
WHERE m.restaurantid = [[your restaurant id goes here]]