mysql - 在LEFT JOIN查询中包括第三张表

我有五个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

结果我得到了一个商店的名单，里面有2号水果（苹果）。
如果usersid=1在正确的商店旁边，我应该怎么做才能在查询中包含shop_activity表以获取用户的状态。像这样的。。。
Shop1，空
商店2，状态：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