MySQL: Select Fields from One Table Referencing Two Other Tables

Question

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.

Might just be a typo, but productid 2 has no subcategories and subcategory 3 has not got a matching category! — RiggsFolly
@PaulKragthorpe whats the problem with the above mentioned query? Whats the expected output — rocks

rocks rocks · Accepted Answer · 2016-10-26T14:53:04

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

MySQL: Select Fields from One Table Referencing Two Other Tables

1 Answers