Обсуждение: how to restrict inner results in OUTER JOIN?
How can I write my query to restrict results from the inside result of an outer join? I'm trying to use a left outer join to remove rows my user doesn't have access permission to see. The plain outer join without a WHERE clause gives me what I want... SELECT * FROM foo f LEFT OUTER JOIN secure_group sg ON (f.group_id = sg.group_id); produces expected results... id | name | group_id | group_id | name ----+--------+----------+----------+----------- 1 | apple | 1 | 1 | group one 4 | orange | 1 | 1 | group one 3 | fish | 2 | 2 | group two 2 | rock | | | 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; But unfortunately, this produces the following results.. id | name | group_id | group_id | name ----+--------+----------+----------+----------- 1 | apple | 1 | 1 | group one 4 | orange | 1 | 1 | group one What do I need to include all the rows for a given group plus the rows that are not assigned to a group. (I'm trying not to use a UNION here, for performance reasons.) I relatively new to SQL, so I'd appreciate any tips, tutorials, instructions, or lessons to help steer me in the right direction here. Thanks, Drew P.S. The table definitions for the examples above are below.. Table "public.foo" Column | Type | Modifiers ----------+-------------------+----------- name | character varying | group_id | oid | Table "public.secure_group" Column | Type | Modifiers ----------+-------------------+----------- group_id | oid | name | character varying | Table "public.securegroup_secureuser" Column | Type | Modifiers ----------+------+----------- group_id | oid | not null user_id | oid | not null Table "public.secure_user" Column | Type | Modifiers ---------+-------------------+----------- user_id | oid | name | character varying |
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@svana.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
Вложения
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
On Mon, 5 May 2003 21:40:10 -0700, Drew Wilson <amw@speakeasy.net> wrote: >I'm trying to use a left outer join to remove rows my user doesn't have >access permission to see. SELECT * FROM foo f LEFT OUTER JOIN secure_group sg ON (f.group_id = sg.group_id AND sg.group_id = 1); Servus Manfred