Re: how to restrict inner results in OUTER JOIN?

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,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
> - Samuel P. Huntington