mysql - 在 LEFT JOIN 查询中包含第三个表

Question

我有五个 mysql 表。
商店

+----+--------------+--------------+| id | name         | address      |+----+--------------+--------------+|  1 | Shop1        | Street1      ||  2 | Shop2        | Street2      ||  3 | Shop3        | Street3      ||  4 | Shop4        | Street4      |+----+--------------+--------------+

fruits

+----+--------------+--------------+| id | fruit        | price        |+----+--------------+--------------+|  1 | Bannana      | 2.5          ||  2 | Apple        | 2.1          ||  3 | Orange       | 1.8          ||  4 | Plum         | 2.2          |+----+--------------+--------------+

availability

+----+--------------+--------------+| id | shop_id      | fruit_id     | +----+--------------+--------------+|  1 | 1            | 2            ||  2 | 2            | 2            ||  3 | 1            | 3            ||  4 | 2            | 1            |+----+--------------+--------------+

shop_activity

+----+--------------+--------------+--------------+| id | shop_id      | user_id      | status       |+----+--------------+--------------+--------------+|  1 | 2            | 1            | 1            ||  2 | 3            | 2            | 1            ||  3 | 1            | 2            | 2            ||  4 | 2            | 2            | 1            |+----+--------------+--------------+--------------+

users

+----+--------------+| id | name         | +----+--------------+|  1 | Peter        ||  2 | John         |+----+--------------+

I have query

SELECT
    availability.shop_id,
    shops.name

FROM availability

LEFT JOIN shops
ON availability.shop_id=shops.id 

WHERE
fruit_id = 2

结果我得到了可以买到 id 为 2(苹果)的水果的商店名单。
如果 users.id = 1 在适当的商店旁边，我应该怎么做才能在查询中包含 shop_activity 表以获取用户状态。像这样的……

Shop1, NULLShop2, status: 1

Answer 1

你可以试试这样的:

SELECT
    availability.shop_id,
    shops.name,
     shop_activity.status
FROM availability
LEFT JOIN shops
ON availability.shop_id=shops.id 
LEFT JOIN shop_activity
ON shop_activity.shop_id = availability.shop_id
   and shop_activity.user_id = 1
WHERE
fruit_id = 2