CODEWITHSUNDEEP
mysqlsqldatabase
MrShoot
MrShoot

Reputation: 863

SQL Query & Multiple results for one record

I'm trying to join multiple tables together but I'm having a bit of trouble when certain products have more than 1 picture attached them them. 

Products                        Pictures
Id                              pic_id
name                            pic_name
picture_id

My query:

SELECT id, name, pic_name
FROM Products 
LEFT OUTER JOIN Pictures p ON id=pic_id

That displays something like

1   RAM  ram.png
1   RAM  ram2.png
1   RAM  ram4.png
2   CPU  test.png

I'd like to display something like

1   RAM  ram.png, ram2.png, ram4, png
2   CPU  test.png

Any help will be greatly appreciated!

Upvotes: 2

Views: 809

Answers (2)

Adam Wenger
Adam Wenger

Reputation: 17540

Something close to this should work for you:

SELECT prod.id, prod.name, GROUP_CONCAT( pic.pic_name )
FROM Products AS prod
LEFT OUTER JOIN Pictures AS pic ON prod.picture_id = pic.pic_id 
GROUP BY prod.id, prod.name

Upvotes: 5

JK0124
JK0124

Reputation: 395

Take a look at this site to see how to use the pivots in mysql: http://en.wikibooks.org/wiki/MySQL/Pivot_table

Upvotes: 0

Related Questions