Paul Kragthorpe Paul Kragthorpe - 1 month ago 8
MySQL Question

MySQL: Select Fields from One Table Referencing Two Other Tables

I have 3 tables: Products, Categories, Product_Category.

Products table has: "id,product_name"


id | product_name
------ | ------------
1 | glove
2 | shirt


Categories table has: "id,category_name"


id | category_name
------ | -------------
1 | cotton
2 | polyester
3 | leather


Product_Category table has "id,product_id,category_id"

id | product_id | category_id
-- | ---------- | -----------
1 | 1 | 1
2 | 1 | 3
3 | 2 | 2


Products can have multiple categories, so I want the following output from the query I run

Output:
Glove - cotton, leather
Shirt - polyester

Here's what I've tried with no luck.

SELECT products.product_name, categories.category_name
FROM product_category
INNER JOIN products on product_category.product_id=products.id
INNER JOIN categories on product_category.category_id=categories.id
LIMIT 0,1000


I realize this is probably an easy query, just can't figure it out.

Answer

Try this

select p.product_name, group_concat(c.category_name, ",") category_name
from Product_Category pc 
join Categories c ON (pc.category_id = c.id)
join Products p ON (pc.product_id = p.id)
group by 1
Comments