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      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