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.
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.
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.
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.
The many-to-many relationship is the only relationship type that uses a junction table.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With