From: "jtx" <jtx@hatesville.com>
To: <pgsql-sql@postgresql.org>
Subject: join/case
Date: Fri, 30 May 2003 16:03:44 -0700
Message-ID: <000001c326ff$b9993f50$c733a8c0@slappy>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: 7bit
Importance: Normal

Hi everyone, I'm trying to do a left join on two tables, mainly because
data from table 'b' (lists) may or may not exist, and if it doesn't I
want results.  However, if data from table lists DOES exist, I want to
run a conditional on it, and then return data based on whether the
conditional is true or false.

Basically, I have something like this:

Select o.id,o.num_purch,o.program from orders o left join lists l on
l.order_id=o.id where o.uid=1 and o.status!='closed'

This query would return something like:

id | num_purch | program
----+-----------+---------
  1 |       100 |       1
  2 |       150 |       2


However, I want to throw an extra conditional in there that says if
l.status='processing', then don't return anything.  So, I tried:

Select o.id,o.num_purch,o.program from orders o left join lists l on
l.order_id=o.id and l.status!='processing' where o.uid=1 and
o.status!='closed'. 

Well, that doesn't work, it returns all the data anyway.  I'm guessing
it's because l.status!='processing' is part of the left join.  

The trick here is that, like I said, there may be NO data from the lists
table, and if not, return everything.  If there is data in lists that
has the order id in it, check to make sure l.status!='processing'. If it
does, don't return it, if it doesn't, return.

Thanks for your help, and sorry if I don't make much sense I tend to
ramble :)