使用SQL连接多对多关系的连接表

Question

我有三个表，其中两个是常规数据表，另一个是多对多连接表。

这两个数据表：

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

我有一张包含商品ID和商店ID的交叉表：

table shops_products

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

我想从店铺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中，感谢任何帮助，我非常感激。

- B_s

Answer 1

我喜欢从外向内开始操作。所以，想象一下所有的列都挤在一个表中，你可以这样写：

SELECT *
FROM products
WHERE shop_id = 3

然后您只需要添加连接以使此语句成为可能。我们知道接下来需要添加联接表（因为它具有product_id，直接连接到products表）。所以这个联接是下一个：

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

- Taryn East

---

谢谢！这解决了我的问题，也帮助我理解了解决方案。再次感谢，等 5 分钟后我会接受它 :) - B_s

正确答案是。只需编辑您的第二个查询，将“shops.shop_name”替换为“shops.shop_location”，因为在OPs的表“shops”中没有这样的列名为shop_name。 - skechav

Answer 2

你可以尝试这个。

SELECT products.product_name, products.product_color
FROM products
INNER JOIN shops_products
ON products.product_id = shops_products.product_id
WHERE shops_products.shop_id = 3

- Vamsi Prabhala

Answer 3

SELECT aa.product_id, aa.product_name, aa.product_color
FROM products AS aa
INNER JOIN shops_products AS bb
ON aa.product_id = bb.procuct_id
WHERE bb.shop_id = 3;

- Kostas Mitsarakis