Re: Full Outer Joins

> -----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 :-(
>
> 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(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant