From: jtx <jtx@hatesville.com>
To: pgsql-sql@postgresql.org
Subject: join/case
Date: Fri, 30 May 2003 16:03:44 -0700
Message-ID: <000001c326ff$b9993f50$c733a8c0@slappy> (raw)
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 :)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jtx@hatesville.com
Subject: Re: join/case
In-Reply-To: <000001c326ff$b9993f50$c733a8c0@slappy>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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