Re: join/case

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(at)postgresql(dot)org so that your
>message can get through to the mailing list cleanly
>
>