Subquery with multiple results combined into a single field?
- by Todd
Assume I have these tables, from which i need to display search results in a browser:
Table: Containers
id | name
1 Big Box
2 Grocery Bag
3 Envelope
4 Zip Lock
Table: Sale
id | date | containerid
1 20100101 1
2 20100102 2
3 20091201 3
4 20091115 4
Table: Items
id | name | saleid
1 Barbie Doll 1
2 Coin 3
3 Pop-Top 4
4 Barbie Doll 2
5 Coin 4
I need output that looks like this:
itemid itemname saleids saledates containerids containertypes
1 Barbie Doll 1,2 20100101,20100102 1,2 Big Box, Grocery Bag
2 Coin 3,4 20091201,20091115 3,4 Envelope, Zip Lock
3 Pop-Top 4 20091115 4 Zip Lock
The important part is that each item type only gets one record/row in the return on the screen. I accomplished this in the past by returning multiple rows of the same item and using a scripting language to limit the output. However, this makes the ui overly complicated and loopy. So, I'm hoping I can get the database to spit out only as many records as there are rows to display.
This example may be a bit extreme because of the 2 joins needed to get to the container from the item (through the sale table).
I'd be happy for just an example query that outputs this:
itemid itemname saleids saledates
1 Barbie Doll 1,2 20100101,20100102
2 Coin 3,4 20091201,20091115
3 Pop-Top 4 20091115
I can only return a single result in a subquery, so I'm not sure how to do this.
