Re: Full Outer Joins

On Monday 27 May 2002 15:33, Joel Burton wrote:
> > -----Original Message-----
> > From: pgsql-novice-owner(at)postgresql(dot)org
> > [mailto:pgsql-novice-owner(at)postgresql(dot)org]On Behalf Of John Taylor
> > Sent: Monday, May 27, 2002 6:15 AM
> > To: PgSQL Novice ML
> > Subject: [NOVICE] Full Outer Joins
> >
> > Can someone give me examples for the correct syntax for FULL
> > OUTER JOIN, where each table
> > has some additional conditions. The docs don't give any complete
> > examples :-(
> >

>
>
> SELECT c.custid,
> c.custname,
> count(o.orderid),
> sum(o.orderamt)
> FROM Customers AS c
> LEFT OUTER JOIN Orders AS o
> ON (o.custid=c.custid
> AND o.part='Pink Widget')
> WHERE c.zip = '20009'
> GROUP BY c.custid,
> c.custname;
>
>

Postgres doesn't seem to like that form with full outer joins ...

SELECT o.stock,o.ordercurrent,o.type,s.stock,s.quantity
FROM orderlines o FULL OUTER JOIN standingorders s ON (s.stock=o.stock AND s.account=' 15048' and s.dayno=2 )
WHERE o.theorder=' 4494' AND (o.type='P' OR o.type='T')

gives ...

ERROR: FULL JOIN is only supported with mergejoinable join conditions

If I join with USING(), and move all the conditionals to the end WHERE that only returns columns matching the first table.

If I join with USING(), and use subselects to generate each side of the JOIN, then it all works fine.

Regards
JohnT