Re: Full Outer Joins

On Monday 27 May 2002 15:33, Joel Burton wrote:
> > -----Original Message-----
> > From: pgsql-novice-owner@postgresql.org
> > [mailto:pgsql-novice-owner@postgresql.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