Re: how to restrict inner results in OUTER JOIN?

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