mysql - SQL连接具有多对多关系的联结表

Question

我有 3 个表，其中 2 个是常规数据表，1 个是多对多联结表。

两个数据表:

table products

product_id | product_name | product_color
-----------------------------------------
1          | Pear         | Green
2          | Apple        | Red
3          | Banana       | Yellow

和

table shops

shop_id    | shop_location
--------------------------
1          | Foo street
2          | Bar alley
3          | Fitz lane

我有一个包含 shop_id 和 product_id 的联结表:

table shops_products

shop_id    | product_id
--------------------
1          | 1
1          | 2
2          | 1
2          | 2
2          | 3
3          | 2
3          | 3

我想从 shop_id 为 3 的商店中的产品中选择数据。我尝试了这里的许多示例，包括连接、左连接、内部连接，但我只是不知道我在这里做什么以及发生了什么错误的。我有以下查询，但无论它们是否在指定的商店中，都返回了所有产品:

SELECT products.product_name, products.product_color
FROM products
LEFT OUTER JOIN shops_products
ON products.product_id = shops_products.product_id
AND shops_products.shop_id = 3
LEFT OUTER JOIN shops
ON shops_products.shop_id = shops.shop_id

预期输出如下:

product_name | product_color
----------------------------
Apple        | Red
Banana       | Yellow

这是在 MySQL 中，感谢您的帮助，我真的很感激。

Answer 1

我喜欢从外面开始，然后搬进去。所以想象一下所有的列都挤在一张表里，你可以这样写:

SELECT *
FROM products
WHERE shop_id = 3

然后您只需添加连接即可使该语句成为可能。我们知道我们接下来需要添加连接表(因为它是直接连接到产品表的表，因为它有 product_id)。所以接下来就是加入:

SELECT products.*
FROM products
INNER JOIN shops_products
ON products.product_id = shops_products.product_id
WHERE shops_products.shop_id = 3

实际上您可以在这里停下来...因为 shop_id 已经存在于连接表中。但是假设您还想在最后一组列中找到商店的位置，然后您将添加商店表联接。

SELECT products.*, shops.shop_location
FROM products
INNER JOIN shops_products
ON products.product_id = shops_products.product_id
INNER JOIN shops
ON shops_products.shop_id = shops.shop_id
WHERE shops_products.shop_id = 3