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.

Answer 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:

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