MySQL join many to many single row

23
votes

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?

2
mysqlsqljoin
Your "joined" section could use clarification - OMG Ponies
@OMG Ponies Ok let me try. What I am wanting to do when I join is the have my product be listed once in a query with all the categories that this product belongs to. Is that even possible? Let me know if this is to vague. - Nathan Stanford II
Update the question, not respond in a comment. - OMG Ponies

2 Answers

43
votes

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.

1
votes

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)