MySQL join many to many single row

Question

I have 3 tables that I want to combine, see below for details:

product

• productID
• name
• price

prod_cat

• productID
• categoryID

category

• categoryID
• name

joined

product.productID category.categoryID product.name product.price category.name(each one though, since a product can belong to more than one category)

What I want to do is get each product with the categories that relate to them in a single query. How would I got about this?

Answer 1

You need two joins:

SELECT     product.productID,     category.categoryID,     product.name,     product.price,     category.name FROM product JOIN product_cat ON product.productID = product_cat.productID JOIN category ON category.categoryID = product_cat.categoryID 

If a product could be in no categories and you still want to return it, change JOIN to LEFT JOIN in both places.

An alternative approach:

SELECT     product.productID,     product.name,     product.price,     GROUP_CONCAT(category.name) FROM product JOIN product_cat ON product.productID = product_cat.productID JOIN category ON category.categoryID = product_cat.categoryID GROUP BY product.productID 

However it might be better just to use two queries instead of putting multiple values into a single cell.

Answer 2

You can use group_concat if using mySQL...

see this thread.

SQL to join one table to another table multiple times? (Mapping products to categories)

(possible duplicate)