CODEWITHSUNDEEP
sqlmysql
Extrakun
Extrakun

Reputation: 19305

MySQL - Displaying results from a sub-query in a single row

I have two tables, products and category. A product can be in multiple categories.

product (<product_id>, name, desc, price)
category (<category_id>, slug, display_name)
product_category(<category_id>, <product_id>)

Product have a N:M relationship with category. I would like a query which show the categories of the product in a single row, like this

name           |   desc                           | categories
------------------------------------------------------------------
The One Ring     One ring to rule them all          Magical Item, Jewelry

Where Magical Item is one category, and Jewelry is another (Not real life example. Sauron is not asking me to set up shop).

So I am thinking of:

SELECT name, desc, categories.display_name FROM product, category, 
category_product  WHERE product.product_id  = category_product.product_id AND
category_product.category_id = category.category_id

However, this will result in the item being listed more than once if it belongs to multiple categories. How to lump all the categories just into one row?

Upvotes: 0

Views: 326

Answers (1)

OMG Ponies
OMG Ponies

Reputation: 332551

Use:

  SELECT p.name,
         p.desc,
         GROUP_CONCAT(c.display_name) AS categories
    FROM PRODUCT p
    JOIN PRODUCT_CATEGORY pc ON pc.product_id = p.product_id
    JOIN CATEGORY c ON c.category_id = pc.category_id
GROUP BY p.name, p.desc

Upvotes: 2

Related Questions