3

Currently I have a database with about 10 tables. I have successfully joined them all together using inner joins and have displayed the results.

However, I am having trouble where one column could have multiple values attributed to it.

For example, my database has this loaded into it: 

item    id
item1 | 1
item2 | 1
item2 | 2
item2 | 3

I joined it like this:

SELECT Main.item, thing.id FROM Main INNER JOIN thing ON Main.MainID = thing.id

I would like to concatenate the three instances of 'id' together for item2, without displaying 'item2' three times on my results page. A delimiter between 'id's might be '&', where the result would be: 

"item1" "1"
"item2" "1 & 2 & 3"

I am pretty sure my problem is in my inadequate use of SQL but I am also using Javascript, PHP, & HTML to display the results so please let me know if you think that might be where the problem is.

Thanks

Mike Stumpf
  • 330
  • 1
  • 11
  • 21
  • can you please add some code of yours so that it would be a little clear – dee Feb 27 '12 at 17:19
  • I've seen some solutions in MS SQL (non standard, which is how I think you'd find most solutions), so I think it might help to mention the database type (MS SQL MySQL, PostgreSQL, Oracle, etc). Otherwise, consider just processing the rows in PHP before you output to the grid (I'm assuming it is a grid). – JayC Feb 27 '12 at 17:25

2 Answers2

2

Just group by your item and use GROUP_CONCAT as aggregate on your ids: 

SELECT items.item, GROUP_CONCAT(ids.id SEPARATOR ' & ')
FROM items
JOIN ids ON items.id=ids.id
GROUP BY items.item

See MySQL GROUP_CONCAT function for more information.

Basti
  • 3,998
  • 1
  • 18
  • 21
0

If you are using MySQL you can create groups (GROUP BY) and then you can use the GROUP_CONCAT MySQL function.

MSSQL "GROUP_CONCAT"

Community
  • 1
  • 1
Peter Kiss
  • 9,309
  • 2
  • 23
  • 38