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
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
If you are using MySQL you can create groups (GROUP BY) and then you can use the GROUP_CONCAT MySQL function.