Re: How to display multiple rows in 1 row

On Tue, Jan 10, 2017 at 12:55 PM, Baxter Allen <baxter(dot)allen(at)gmail(dot)com>
wrote:

> 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:
>

​If you can add "individual" as a FK on tables B and C - and make it a PK
on table A - your life would become a whole lot easier.

>
> 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
>
​​
De-normalize table_a to match your desired output:

WITH recast_table_a AS (
​SELECT inds.individual,
(SELECT _id FROM table_a WHERE table_a.individual = inds.individual AND _id
~ 'table_b') AS b_id​,
(SELECT _id FROM table_a WHERE table_a.individual = inds.individual AND _id
~ 'table_c') AS c_id​,
FROM (​SELECT DISTINCT individual​ FROM table_a) inds
​)

Then join in the other tables:
SELECT *
FROM recast_table_a
LEFT JOIN table_b ON (b_id = ​table_b._id)
LEFT JOIN table_c ON (c_id = table_c._id)

David J.