SQL join on junction table with many to many relation

Question

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

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:

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:

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.

Taryn East · Accepted Answer · 2018-10-15T04:26:06.547

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:

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's location in the set of final columns, you'd then add the shop-table join.

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

Thank you! This solved my problem, and helped me understand the solution. Thanks again, I'll accept it in 5 minutes when it lets me :) — B_s, Oct 28 '15 at 23:54
Correct answer . Just edit your second query replacing "shops.shop_name" with shops.shop_location" as the is no such column as shop_name in the OPs' table "shops". — skechav, Oct 14 '18 at 21:29

score 1 · Answer 2 · answered Oct 28 '15 at 23:45

1

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

answered Oct 28 '15 at 23:45

Vamsi Prabhala

48,685
4
36
58

score 0 · Answer 3 · answered Oct 28 '15 at 23:46

0

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;

answered Oct 28 '15 at 23:46

Kostas Mitsarakis

4,772
3
23
37

SQL join on junction table with many to many relation

3 Answers3