|
Posted by Rolf Ψstvik on 03/29/05 12:45
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
[Back to original message]
|