Date: Sat, 31 May 2003 08:08:31 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: jtx <jtx@hatesville.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: join/case
Message-ID: <20030531130831.GA31995@wolff.to>
Mail-Followup-To: jtx <jtx@hatesville.com>, pgsql-sql@postgresql.org
References: <000001c326ff$b9993f50$c733a8c0@slappy>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <000001c326ff$b9993f50$c733a8c0@slappy>
User-Agent: Mutt/1.5.4i

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