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