From: | Drew Wilson <amw(at)speakeasy(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | how to restrict inner results in OUTER JOIN? |
Date: | 2003-05-06 04:40:10 |
Message-ID: | D1C4526A-7F7C-11D7-946A-000393D3E482@speakeasy.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 |
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2003-05-06 05:11:48 | Re: how to restrict inner results in OUTER JOIN? |
Previous Message | Bruno Wolff III | 2003-05-06 01:31:27 | Re: Many little databases or one big one? |