Thread: join/case

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 :)

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
>  
>

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.

On Fri, May 30, 2003 at 16:03:44 -0700, jtx <jtx@hatesville.com> wrote:
> 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'
> 
> However, I want to throw an extra conditional in there that says if
> l.status='processing', then don't return anything.  So, I tried:

The straight forward way to do this is do just do what you described.
Make the join a subselect (keeping l.status) and then select from that
where status <> 'processing. The result looks like:

select id, num_purch, program from   (select o.id,o.num_purch,o.program,l.status     from orders o left join lists l on
l.order_id=o.id    where o.uid=1 and o.status!='closed') as j where   status <> 'processing';