From: | Steve Midgley <science(at)misuse(dot)org> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | Baxter Allen <baxter(dot)allen(at)gmail(dot)com>, "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-11 03:11:19 |
Message-ID: | CAJexoSL3vBFfSdyEoqBP2-JWwV+i5vH9F+Ek+=m-eOo_kom3Ug@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Tue, Jan 10, 2017 at 12:24 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> 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.
>
> Hi,
Another approach: I think the problem is that you're joining table_b and
table_c with the same join statement, which isn't quite right. Doing it
that way causes table_a to match either table_b or table_c, but never both
- so you get the "interleaved" effect on your data that you are trying to
avoid. By joining table_a and table_b first, you create a single "virtual"
table, then joining that to table_c causes c to be appended to the end of
this whole table.
Given the DDL at the bottom of my email (what I used to test), this sql
statement works, I think:
select * from
(select a.id as a_id, * from table_a a
left join table_b b on a.id=b.id) a1
join table_c c on a1.a_id = c.id
Returns
"1.table_c";"1.table_c";1;"";;;"1.table_c";4;3
"2.table_c";"2.table_c";2;"";;;"2.table_c";3;4
"3.table_c";"3.table_c";3;"";;;"3.table_c";2;21
I hope that helps?
Steve
p.s. Minor point, but if you provide some DDL for your tables and data on
future questions it makes it easier to come up with a solution.
--
-- TOC entry 185 (class 1259 OID 16411)
-- Name: table_a; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE table_a (
id text NOT NULL,
individual integer
);
ALTER TABLE table_a OWNER TO postgres;
--
-- TOC entry 186 (class 1259 OID 16419)
-- Name: table_b; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE table_b (
id text NOT NULL,
vala integer,
valb integer
);
ALTER TABLE table_b OWNER TO postgres;
--
-- TOC entry 187 (class 1259 OID 16440)
-- Name: table_c; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE table_c (
id text NOT NULL,
valc integer,
vald integer
);
ALTER TABLE table_c OWNER TO postgres;
--
-- TOC entry 2133 (class 0 OID 16411)
-- Dependencies: 185
-- Data for Name: table_a; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY table_a (id, individual) FROM stdin;
1.table_b 1
2.table_b 2
3.table_b 3
1.table_c 1
2.table_c 2
3.table_c 3
\.
--
-- TOC entry 2134 (class 0 OID 16419)
-- Dependencies: 186
-- Data for Name: table_b; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY table_b (id, vala, valb) FROM stdin;
1.table_b 1 2
2.table_b 4 7
3.table_b 5 4
\.
--
-- TOC entry 2135 (class 0 OID 16440)
-- Dependencies: 187
-- Data for Name: table_c; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY table_c (id, valc, vald) FROM stdin;
1.table_c 4 3
2.table_c 3 4
3.table_c 2 21
\.
--
-- TOC entry 2011 (class 2606 OID 16418)
-- Name: table_a table_a_pkey; Type: CONSTRAINT; Schema: public; Owner:
postgres
--
ALTER TABLE ONLY table_a
ADD CONSTRAINT table_a_pkey PRIMARY KEY (id);
--
-- TOC entry 2013 (class 2606 OID 16426)
-- Name: table_b table_b_pkey; Type: CONSTRAINT; Schema: public; Owner:
postgres
--
ALTER TABLE ONLY table_b
ADD CONSTRAINT table_b_pkey PRIMARY KEY (id);
--
-- TOC entry 2015 (class 2606 OID 16447)
-- Name: table_c table_c_pkey; Type: CONSTRAINT; Schema: public; Owner:
postgres
--
ALTER TABLE ONLY table_c
ADD CONSTRAINT table_c_pkey PRIMARY KEY (id);
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2017-01-11 03:40:57 | Re: How to display multiple rows in 1 row |
Previous Message | David G. Johnston | 2017-01-10 20:24:12 | Re: How to display multiple rows in 1 row |