Re: How to display multiple rows in 1 row

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);