CODEWITHSUNDEEP
phpsql
Jo H
Jo H

Reputation: 1106

group data into one variable from sql

Not too sure if the title to this actually explains what I really need to ask, so I'm sorry about that.

Basically, I have two tables (products and stock).

In products, I have two products:

ID: 1 || Name: Top
ID: 2 || Name: Bottom

In stock, I have five stock lines:

ID: 1 || ProductID: 1 || Size: Medium
ID: 2 || ProductID: 1 || Size: Large
ID: 3 || ProductID: 2 || Size: Medium
ID: 4 || ProductID: 3 || Size: 7
ID: 5 || ProductID: 3 || Size: 8

What I need to do is pull out all products with stock, therefore my code at the moment is:

SELECT p.ID, p.Name, s.Size FROM products p JOIN stock s ON s.ProductID = p.ID

This is then pulling the following out:

ID: 1 || Name: Top || Size: Medium
ID: 1 || Name: Top || Size: Large
ID: 2 || Name: Bottom || Size: Medium
ID: 3 || Name: Shoes || Size: 7
ID: 3 || Name: Shoes || Size: 8

What I want to do with this is put the sizes in one column (comma delimited) per ID - thus I want it to look like:

ID: 1 || Name: Top || Size: Medium, Large
ID: 2 || Name: Bottom || Size: Medium
ID: 3 || Name: Shoes || Size: 7, 8

Do anyone know how I can do this? I should know how to do it but my mind has just gone blank!

Upvotes: 1

Views: 139

Answers (2)

blaff
blaff

Reputation: 304

SELECT p.ID, p.Name, GROUP_CONCAT(s.Size) AS Size 
FROM products p 
JOIN stock s ON s.ProductID = p.ID 
GROUP BY p.ID

That should work fine.

Upvotes: 1

user1027167
user1027167

Reputation: 4458

use GROUP_CONCAT if your database is mysql.

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

Upvotes: 0

Related Questions