Message-ID: <3ED7EEF1.8080402@openratings.com>
Date: Fri, 30 May 2003 19:53:21 -0400
From: Dmitry Tkach <dmitry@openratings.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20021130
MIME-Version: 1.0
To: jtx <jtx@hatesville.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: join/case
References: <000001c326ff$b9993f50$c733a8c0@slappy>
In-Reply-To: <000001c326ff$b9993f50$c733a8c0@slappy>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

I think, something like this should work:

select o.id,o.num_purch,o.program from orders o left join lists l on
(l.order_id=o.id) where 
(l.status is null or l.status!='processing') and o.uid=1 and o.status!='closed'. 

(l.status is null should take care about the case when there is no matching row for the join)...

I hope, it helps...

Dima


jtx wrote:

>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 :)
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly
>  
>