Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 97367475864 for ; Tue, 19 Mar 2002 23:08:35 -0500 (EST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 91EB0D5F7; Tue, 19 Mar 2002 20:08:39 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 879D05BFC; Tue, 19 Mar 2002 20:08:39 -0800 (PST) Date: Tue, 19 Mar 2002 20:08:39 -0800 (PST) From: Stephan Szabo To: Patrick Hatcher Cc: Subject: Re: What is Syntax for multiple FULL OUTER JOINS? In-Reply-To: Message-ID: <20020319200610.W40360-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200203/250 X-Sequence-Number: 7129 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.