SQL join in a join table with many, many relationships

Question

I have three tables, of which 2 are regular data tables, and 1 is many, many connection tables.

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 connection table that contains shop_id 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 the store with shop_id 3. I tried many examples here with joins, left joins, inner joins, but I just don't know what I'm doing here and what is going wrong. The request that I had but just returned all the products, regardless of whether they are in the specified store, is as follows:

 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 conclusion is as follows:

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

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

Answer 1

I like to start from the side and move. So, imagine that all the columns were crammed at just one table, you could write something like:

 SELECT * FROM products WHERE shop_id = 3 

You just need to add unions to make this statement possible. We know that we need to add the following join table (like the one that joins directly to the product table due to the presence of product product_id in it). So this connection is as follows:

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

and in fact you can stop right here ... because shop_id already exists in the connection table. But let's say you also wanted the store name in the set of leaf columns, then you added the shop-table connection.

 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 

Answer 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 

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;