Re: Full Outer Joins

> -----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 :-(
>
> I can only get it to work using subselects:
>
> SELECT stock,o.ordercurrent,s.quantity FROM
> (SELECT stock,ordercurrent FROM orderlines o WHERE  o.theorder='
> 4494' AND (o.type='P' OR o.type='T')) AS o
> FULL OUTER JOIN
> (SELECT stock,quantity FROM standingorders s WHERE s.account='
> 15048' AND s.dayno=2) AS s
> USING (stock)

create table Customers (custid int primary key,
                        custname text unique,
                        zip char(5) not null);

create table Orders (orderid int primary key,
                     custid int not null references Customers,
                     part text not null,
                     orderamt int not null);

insert into Customers values (1,'Joel','20009');
insert into Customers values (2,'Johnny Cheapskate','20009');
insert into Customers values (3,'Santa Claus','10005');

insert into Orders values (1,1,'Pink Widget',100);
insert into Orders values (2,1,'Pink Widget',200);
insert into Orders values (3,3,'Pink Widget',100);

Select customer id, name, # of orders, and total $ of orders, only for those
customers in zip 20009 and only for Pink Widgets. However, be sure to show
all customers in that zip code; for those that never ordered a Pink Widget,
show zero).


           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;


 custid |     custname      | count | sum
--------+-------------------+-------+-----
      1 | Joel              |     2 | 300
      2 | Johnny Cheapskate |     0 |
(2 rows)


If this doesn't give you the idea, you'll have to be more specific about
where you're stuck.

- J.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant