Re: collapse SELECT from multiple tables

davidc@connect4less.com (David Christensen) wrote in news:1111687408.7718.85.camel@pluto.connect4less.com: > I'm trying to figure out the best way to handle a SELECT from multiple > tables where a single ID in tableA relates to multiple ID's in tableB: > > "SELECT tableA.ID, tableB.data FROM tableA, tableB WHERE tableA.ID=3 > AND tableA.ID=tableB.tableAID" > > What I'm trying to product is an array output similar to: > >| ID | data | >| 3 | data1, data2, data3 | > > if the table data is represented by this: > > tableA >| ID | something | >| 1 | something1 | >| 2 | something2 | >| 3 | something3 | > > tableB >| ID | tableAID | data | >| 1 | 3 | data1 | >| 2 | 3 | data2 | >| 3 | 3 | data2 | Suggestion 1 and 2 may depend of which database you use. Suggestion 3 have been touched in other answers. Suggestion 1: Create a custom function which takes the tableA.ID as parameter and run a query on tableB and creates an string as answer. I do this in an application i use PostgreSQL. Suggestion 2: Create an aggregate. I havent yet tried this in PostgreSQL. Suggestion 3: Order your sql result and loop over the data. -- Rolf Østvik