Re: how to restrict inner results in OUTER JOIN? - Mailing list pgsql-general

From Nigel J. Andrews
Subject Re: how to restrict inner results in OUTER JOIN?
Date
Msg-id Pine.LNX.4.21.0305060737520.10245-100000@ponder.fairway2k.co.uk
Whole thread Raw
In response to Re: how to restrict inner results in OUTER JOIN?  (Martijn van Oosterhout <kleptog@svana.org>)
List pgsql-general
On Tue, 6 May 2003, Martijn van Oosterhout wrote:

> On Mon, May 05, 2003 at 09:40:10PM -0700, Drew Wilson wrote:
> > Now, I would like to exclude all rows whose group_id is NOT 1, but
> > include the rows whose group_id is NULL.
> >
> > I thought adding a WHERE clause would get me what I want...
> >     SELECT * FROM foo f LEFT OUTER JOIN secure_group sg
> >            ON (f.group_id = sg.group_id) WHERE sg.group_id = 1;
>
> How about:
>
>      SELECT * FROM foo f LEFT OUTER JOIN secure_group sg
>             ON (f.group_id = sg.group_id)
>             WHERE ( sg.group_id = 1 or sg.group_id IS NULL );
>
> Hope this helps,

Except you made the same typo mistake in the original question, which was to
miss out the NOT part of the "group_id is NOT 1" condition.

So...
  SELECT * FROM foo f LEFT OUTER JOIN secure_group sg
         ON (f.group_id = sg.group_id)
         WHERE ( sg.group_id <> 1 or sg.group_id IS NULL );


--
Nigel J. Andrews


pgsql-general by date:

Previous
From: Martijn van Oosterhout
Date:
Subject: Re: Database server restarting
Next
From: "shoaib"
Date:
Subject: Re: Database server restarting