连接表上的SQL连接具有多对多关系

I have three tables, of which 2 are regular data tables and 1 is a many to many junction table.

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

The two data tables:

两个数据表:

table products

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

and

table shops

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

I have a junction table which contains the shop_id's and product_id's:

我有一个包含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

I want to select data from products that are in shop with shop_id 3. I tried many examples from here with joins, left joins, inner joins, but I just don't know what I'm doing here and what is going wrong. The query I had, but just returned all products regardless if they are in the specified shop is the following:

我想从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

The expected output is the following:

预期的输出如下:

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

This is in MySQL, thank you for any help, I really appreciate it.

这是在MySQL,谢谢你的任何帮助,我真的很感激。

3 个解决方案

#1

I like to start from the outside and move in. So imagine all the columns were all jammed together in just one table, you could write something like:

我喜欢从外面开始进入。所以想象所有的列都只是在一张桌子里塞在一起,你可以这样写:

SELECT *
FROM products
WHERE shop_id = 3

You then just need to add the joins to make this statement possible. We know we need to add the join table next (as it's the one that joins directly onto the products table due to it having the product_id in it). So that join is what goes next:

然后,您只需添加连接即可使此语句成为可能。我们知道我们需要接下来添加连接表(因为它是直接连接到products表的,因为它有product_id)。所以接下来是接下来的事情:

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

and actually you can stop right here... because shop_id exists on the join table already. But lets say you also wanted the shop name in the set of final columns, you'd then add the shop-table join.

实际上你可以在这里停止...因为shop_id已经存在于连接表中。但是,假设您还希望在最终列的集合中使用商店名称,然后添加商店表连接。

SELECT products.*, shops.shop_name
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

#2

You can try this.

你可以试试这个。

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

#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;

#1