SQL Help ~ Relational Database Model ~ Displaying Results

Question

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

Answer 1

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.