Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cRA7n-0001gg-IB for pgsql-sql@arkaria.postgresql.org; Wed, 11 Jan 2017 04:02:59 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cRA7m-0000Hs-QI for pgsql-sql@arkaria.postgresql.org; Wed, 11 Jan 2017 04:02:58 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cRA6p-0007fL-WF for pgsql-sql@postgresql.org; Wed, 11 Jan 2017 04:02:00 +0000 Received: from sanddollar.geekisp.com ([216.168.135.167]) by makus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cRA6n-0003Nw-4n for pgsql-sql@postgresql.org; Wed, 11 Jan 2017 04:01:58 +0000 Received: (qmail 2592 invoked by uid 1003); 11 Jan 2017 04:01:54 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=default; d=misuse.org; b=CYMvG49Pv+HAFKsMcmJbu0yZu6mLtyiafTMsweFf7TTftRvPtk7vmjatqvHBlcU+tMuBaC7jea/ksMK+ypZz3bElLNAxAX6wyHUuBVtEBAeO5rW5isCgBWxdg5TPnFda ; Received: from unknown (HELO mail-oi0-f43.google.com) (steve@misuse.org@209.85.218.43) by mail.geekisp.com with (AES128-SHA encrypted) SMTP; 11 Jan 2017 04:01:54 -0000 Received: by mail-oi0-f43.google.com with SMTP id u143so163837540oif.3 for ; Tue, 10 Jan 2017 20:01:54 -0800 (PST) X-Gm-Message-State: AIkVDXJCp3CR2pBpqUcFzOPnJyWMLJ0GxSNv1+mwcnUNJHNsm3//JrkQRoGSzxsAwtAr9AVD7JcISQZVr6A2DQ== X-Received: by 10.202.183.6 with SMTP id h6mr3224432oif.76.1484107313526; Tue, 10 Jan 2017 20:01:53 -0800 (PST) MIME-Version: 1.0 Received: by 10.182.142.5 with HTTP; Tue, 10 Jan 2017 20:01:52 -0800 (PST) In-Reply-To: References: From: Steve Midgley Date: Tue, 10 Jan 2017 20:01:52 -0800 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: How to display multiple rows in 1 row To: "David G. Johnston" Cc: Baxter Allen , "pgsql-sql@postgresql.org" Content-Type: multipart/alternative; boundary=001a113cd1ec3b3db50545c9a9de X-Pg-Spam-Score: -1.5 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --001a113cd1ec3b3db50545c9a9de Content-Type: text/plain; charset=UTF-8 On Tue, Jan 10, 2017 at 7:40 PM, David G. Johnston < david.g.johnston@gmail.com> wrote: > On Tuesday, January 10, 2017, Steve Midgley wrote: > >> >> 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 >> >> > Aside from executing without error I don't see how this works at all. > You cannot solve the posed problem by rearranging simple joins. > > David J. > Ugh - my bad - I only checked the table_c data in my results and got what I was expecting. I'm out of time tonight to play with this - apologies for bogus response. I'll see if I can rectify tomorrow. --001a113cd1ec3b3db50545c9a9de Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

= On Tue, Jan 10, 2017 at 7:40 PM, David G. Johnston <david.g.johns= ton@gmail.com> wrote:
On Tuesday, January 10, 2017, Steve Midgley <science@misuse.org> wrote:<= br>

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=3Db.id) a1
join table_c c on a1.a_i= d =3D c.id
=


Returns

"1.table_c";"1.table_c&quo= t;;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;&qu= ot;";;;"3.table_c";2;21


Aside from executing= =C2=A0without error I don't see how this works at all. You=C2=A0cannot = solve the posed problem by rearranging simple joins.

David J.

Ugh - my bad - I only che= cked the table_c data in my results and got what I was expecting. I'm o= ut of time tonight to play with this - apologies for bogus response. I'= ll see if I can rectify tomorrow.

--001a113cd1ec3b3db50545c9a9de--