Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-02-06 : 18:13:30
|
brad writes "I have a table with a bunch of records, Table A. I have another table, Table B, with a many-to-one relationship with the records in Table A. Is there a way, with one SQL statement, to pull out the related data of Table A and Table B, where the resulting set will have
all the data related to each record of A in one row?
That is, Table A might have two records:
| key_id | col1 | col2 |
| 1 | val1 | val2 |
| 2 | val2 | val2 |
Table B, can have several records for each unique key_id of A:
| key_id references A | primary_key_id |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 4 |
| 2 | 5 |
.
.
.
where the B.primary_key_id is referenced by values in Table C:
| primary_key_id references B | value |
| 1 | 100 |
| 2 | 450 |
| 3 | 500 |
Now, I pull out the records from A, then return to the db for each record of A to pull out the data from B and C. This creates too many trips back to the db and slows things down a lot.
What I need is a way, hopefully with one statement, to pull all the data out in the following way:
| A.key_id | A.col1 | A.col2 | B.primary_key_id_1 | C.primary_key_id_1.value | B.primary_key_id_2 | C.primary_key_id_2.value | B.primary_key_id_3 | C.primary_key_id_3.value |
| 1 | val1 | val2 | 1 | 100 | 2 | 450 | 3 | 500 |
" |
|
|
|
|
|
De-Normalizing Data With one SQL Statement