Hello,
I have a database with a large number of individuals, and for each of these individuals there are entries in multiple tables as follows:
| table_a | |
| _id | individual |
| 1.table_b | 1 |
| 2.table_b | 2 |
| 3.table_b | 3 |
| 1.table_c | 1 |
| 2.table_c | 2 |
| 3.table_c | 3 |
| table_b | | |
| _id | valA | valB |
| 1.table_b | 1 | 2 |
| 2.table_b | 4 | 7 |
| 3.table_b | 5 | 4 |
| table_c | | |
| _id | valC | valD |
| 1.table_c | 4 | 3 |
| 2.table_c | 3 | 4 |
| 3.table_c | 2 | 21 |
Using the following code:
Select distinct C.individual, A.*, B.*
FROM table_a A
LEFT JOIN table_b B ON A._id = B._id
LEFT JOIN table_c C ON A._id = C._id
order by A.individual
I get the following output:
| individual | _id | valA | valB | _id | valC | valD |
| 1 | 1.table_b | 1 | 2 | | | |
| 1 | | | | 1.table_c | 4 | 3 |
| 2 | 2.table_b | 4 | 7 | | | |
| 2 | | | | 2.table_c | 3 | 4 |
| 3 | 3.table_b | 5 | 4 | | | |
| 3 | | | | 3.table_c | 2 | 21 |
Instead, I would like the output for each individual to be all on a single row:
| individual | _id | valA | valB | _id | valC | valD |
| 1 | 1.table_b | 1 | 2 | 1.table_c | 4 | 3 |
| 2 | 2.table_b | 4 | 7 | 2.table_c | 3 | 4 |
| 3 | 3.table_b | 5 | 4 | 3.table_c | 2 | 21 |
Any idea how I could make that happen?
Thanks!
Baxter