Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL join on junction table with many to many relation

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.

like image 399
B_s Avatar asked Oct 28 '15 23:10

B_s


People also ask

How does a junction table handle a many-to-many relationship?

When you need to establish a many-to-many relationship between two or more tables, the simplest way is to use a Junction Table. A Junction table in a database, also referred to as a Bridge table or Associative Table, bridges the tables together by referencing the primary keys of each data table.

How do I join many-to-many tables in SQL?

In this case the two tables are joined using the relationship table1.id = table2.id . It is possible to use multiple join statements together to join more than one table at the same time. To do that you add a second INNER JOIN statement and a second ON statement to indicate the third table and the second relationship.

How do you handle a many-to-many relationship?

When you have a many-to-many relationship between dimension-type tables, we provide the following guidance: Add each many-to-many related entity as a model table, ensuring it has a unique identifier (ID) column. Add a bridging table to store associated entities. Create one-to-many relationships between the three tables.

What type of relationship requires the use of a junction table?

The many-to-many relationship is the only relationship type that uses a junction table.


2 Answers

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
like image 125
Taryn East Avatar answered Sep 28 '22 04:09

Taryn East


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
like image 23
Vamsi Prabhala Avatar answered Sep 28 '22 06:09

Vamsi Prabhala