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