CODEWITHSUNDEEP
mysqlsql
Brandon - Free Palestine
Brandon - Free Palestine

Reputation: 16656

SQL Joins with one to many relationship - Combine the many into one row?

I have a product table and a price table where a product can have several price rows. Is it possible to do a JOIN statement that returns one row per product with the price column from each price row where product is equal to the product ID?

Right now I have this:

SELECT PT.*, PR.* 
FROM `products` AS PT 
RIGHT JOIN `prices` PR 
    ON PR.`product` = PT.`ID`

And if there are 4 price rows for one product, I get 4 rows in response.

Upvotes: 1

Views: 1614

Answers (1)

O. Jones
O. Jones

Reputation: 108651

Look up the wizzbang GROUP_CONCAT function, which does what you want quite nicely (making a delimiter-separated string).

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

SELECT pt.`id`, 
       group_concat(pr.`price` separator ',') 
  FROM `products` AS PT  
  LEFT JOIN `prices` PR ON PR.`product` = PT.`ID`
  GROUP BY pt.`id`

Upvotes: 4

Related Questions