Re: join/case

On Fri, 30 May 2003, 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.

My first thought was something like:

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' and
(l.status!='processing' or l.status is null);

But that's probably not what you want if there can be multiple rows in
lists that refer to the same row in orders.