Received: from fddlnint05.fds.com (fddlnint05.fds.com [208.15.91.52]) by postgresql.org (Postfix) with ESMTP id 5222A475BF8 for ; Wed, 20 Mar 2002 12:08:24 -0500 (EST) Subject: Re: What is Syntax for multiple FULL OUTER JOINS? To: "Stephan Szabo Cc: pgsql-sql@postgresql.org X-Mailer: Lotus Notes Release 5.0.6 December 14, 2000 Message-ID: From: "Patrick Hatcher" Date: Wed, 20 Mar 2002 09:04:01 -0800 X-MIMETrack: Serialize by Router on FDDLNINT05/FSG/SVR/FDD(Release 5.0.4 |June 8, 2000) at 03/20/2002 12:07:45 PM MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii X-Archive-Number: 200203/264 X-Sequence-Number: 7143 Thanks that did it. Patrick Hatcher Macys.Com Legacy Integration Developer 415-932-0610 office HatcherPT - AIM Stephan Szabo panda.com> cc: Subject: Re: [SQL] What is Syntax for multiple FULL OUTER 03/19/2002 08:08 PM JOINS? On Tue, 19 Mar 2002, Patrick Hatcher wrote: > Is it possible to do a full outer join on 3 or more queries? I figured out > how to do 2, but I get an error message when trying to do three > > > Example: > > (works) > select coalesce(a.f1, b.f1) as col_1, b.f2 as col_2 > from > (select f1, f2 from table1 where f3 =1) a > FULL OUTER JOIN > (select f1, f2 from table1 where f3 =2) b > on a.f1 = b.f1 > > (does not work) > select coalesce(a.f1, b.f1,c.f1) as col_1, b.f2 as col_2, c.f2 as col_3 > from > (select f1, f2 from table1 where f3 =1) a > FULL OUTER JOIN > (select f1, f2 from table1 where f3 =2) b > FULL OUTER JOIN > (select f1, f2 from table1 where f3 =3) c > on a.f1 = b.f1and a.f1 = c.f1 You need separate on clauses for each join: select ... from ( (select f1, f2 from table1 where f3=1) a full outer join (select f1, f2 from table1 where f3=2) b on a.f1=b.f1) full outer join (select f1,f2 from table1 where f3=3) c on a.f1=c.f1 should do it I think.