From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Baxter Allen <baxter(dot)allen(at)gmail(dot)com> |
Cc: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: How to display multiple rows in 1 row |
Date: | 2017-01-10 20:24:12 |
Message-ID: | CAKFQuwZZnTXhqpLc8LtJ90t=sCOyK0FxPRJg9wWkvR_dy1HX4w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Midgley | 2017-01-11 03:11:19 | Re: How to display multiple rows in 1 row |
Previous Message | Baxter Allen | 2017-01-10 19:55:07 | How to display multiple rows in 1 row |