Subquery with multiple results combined into a single field?

Posted by Todd on Stack Overflow See other posts from Stack Overflow or by Todd
Published on 2010-06-16T18:25:09Z Indexed on 2010/06/16 18:42 UTC
Read the original article Hit count: 203

Filed under:

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.

© Stack Overflow or respective owner

Related posts about sql