pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedjoin/case
4+ messages / 4 participants
[nested] [flat]
* join/case
@ 2003-05-30 23:03 jtx <jtx@hatesville.com>
0 siblings, 3 replies; 4+ messages in thread
From: jtx @ 2003-05-30 23:03 UTC (permalink / raw)
To: pgsql-sql
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 :)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: join/case
@ 2003-05-30 23:53 Dmitry Tkach <dmitry@openratings.com>
parent: jtx <jtx@hatesville.com>
2 siblings, 0 replies; 4+ messages in thread
From: Dmitry Tkach @ 2003-05-30 23:53 UTC (permalink / raw)
To: jtx <jtx@hatesville.com>; +Cc: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: join/case
@ 2003-05-31 00:04 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: jtx <jtx@hatesville.com>
2 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2003-05-31 00:04 UTC (permalink / raw)
To: jtx <jtx@hatesville.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: join/case
@ 2003-05-31 13:08 Bruno Wolff III <bruno@wolff.to>
parent: jtx <jtx@hatesville.com>
2 siblings, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2003-05-31 13:08 UTC (permalink / raw)
To: jtx <jtx@hatesville.com>; +Cc: pgsql-sql
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';
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-05-31 13:08 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-30 23:03 join/case jtx <jtx@hatesville.com>
2003-05-30 23:53 ` Dmitry Tkach <dmitry@openratings.com>
2003-05-31 00:04 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-05-31 13:08 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox