Re: Building a "complex" select?

From: Scott Marlowe <smarlowe(at)g2switchworks(dot)com>
To: Bjørn T Johansen <btj(at)havleik(dot)no>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Building a "complex" select?
Date: 2005-04-18 17:05:42
Message-ID: 1113843941.20921.510.camel@state.g2switchworks.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

If that doesn't work, you'll need a subselect:

select * from
(select ordre.id, ordre.desc, log_stop.stoptype from ordre left outer
join log_stop on
ordre.id = log_stop.ordreid where ordre.id = 22) as a
were a.stoptype=1 or a.stoptype IS NULL

On Mon, 2005-04-18 at 11:58, Scott Marlowe wrote:
> Then maybe:
>
> AND (log_stop.stoptype=1 OR log_stop.stoptype IS NULL)
>
> ??
>
> On Mon, 2005-04-18 at 11:55, Bjørn T Johansen wrote:
> > Nope... It returns 0 rows when there are no rows in log_stop with stoptype = 1...
> >
> > BTJ
> >
> > Scott Marlowe wrote:
> > > On Mon, 2005-04-18 at 11:24, Bjørn T Johansen wrote:
> > >
> > >>I need a select like this..:
> > >>
> > >>select ordre.id, ordre.desc, log_stop.stoptype from ordre left outer join log_stop on
> > >>ordre.id = log_stop.ordreid where ordre.id = 22
> > >>
> > >>The problem is that I need to include "where log_stop.stoptype = 1". So if log_stop
> > >>includes 3 rows with stoptype = 1 and ordreid = 22, my select should return 3 rows.
> > >>Also, if log_stop includes 3 rows with stoptype = 1 and 2 rows with stoptype = 2, my
> > >>select should still return 3 rows. And if there are 0 rows with stoptype = 1, my
> > >>select should return 1 row.
> > >
> > >
> > > Would this work?
> > >
> > > select ordre.id, ordre.desc, log_stop.stoptype from ordre left outer join log_stop on
> > > ordre.id = log_stop.ordreid where ordre.id = 22 AND COALESCE(log_stop.stoptype,1)=1
> > >
> > >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2005-04-18 17:07:52 Re: Building a "complex" select?
Previous Message Bjørn T Johansen 2005-04-18 17:05:22 Re: Building a "complex" select?