2

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!

Adam Wenger
  • 17,100
  • 6
  • 52
  • 63
MrShoot
  • 843
  • 1
  • 9
  • 21
  • 1
    Search for `sql pivot` on this site. – Oded Dec 04 '11 at 19:53
  • You could also possibly use the COALESCE function. What database server are you using please? – dash Dec 04 '11 at 19:56
  • 1
    I was going to post this as an answer, but there is already a post on Stack Overflow: http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field - the GROUP_CONCAT function. – dash Dec 04 '11 at 20:07

2 Answers2

5

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
Adam Wenger
  • 17,100
  • 6
  • 52
  • 63
  • It's achieving the same results in my original post. Listing as 1 RAM ram.png 1 RAM ram2.png 1 RAM ram4.png 2 CPU test.png – MrShoot Dec 04 '11 at 20:08
  • 1
    You can avoid the join if you use the GROUP_CONCAT function - been using it a lot recently, very similar to the hacky use of COALESCE in SQL Server :-) – dash Dec 04 '11 at 20:09
  • I don't want to see the id repeated in rows. Just 1 row, if it contains multiple pictures that they show in "pictures" header all together. – MrShoot Dec 04 '11 at 20:09
  • where is it getting the pic_name from you're not referencing Pictures table – MrShoot Dec 04 '11 at 20:14
  • 1
    @ManuelDiaz I've updated my answer, I believe this should be what you're hoping for – Adam Wenger Dec 04 '11 at 20:15
  • Absolutely right, that's why I upvoted yours. Actually just wanted to point out the missing Group By. – dash Dec 04 '11 at 20:16
  • Thanks both for the amazing help! Some pretty nifty stuff here :) – MrShoot Dec 04 '11 at 20:16
  • @Adam Wenger- Group by is on prod.id, prod.name, How to pass pic.pic_name to a function in select without group on pic.pic_name? – Reza ArabQaeni Dec 04 '11 at 20:25
  • @RedHat Columns you are passing to aggregate functions do not need to be in `GROUP BY`; it is the other columns not in an aggregate function that need to be in a `GROUP BY`. – Adam Wenger Dec 04 '11 at 20:28
  • @Adam Wenger- Hmm, I think GROUP_CONCAT is a user function! I not see beforehand. Thanks, I voted up. – Reza ArabQaeni Dec 04 '11 at 20:32
  • @RedHat [GROUP_CONCAT is described here for MySQL](http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat) – Adam Wenger Dec 04 '11 at 20:37
0

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

JK0124
  • 375
  • 2
  • 4
  • 14