On Wed, 26 Jan 2005 07:16:25 -0800, "Peter Darley"
<[EMAIL PROTECTED]> wrote:
>SELECT User_ID
>FROM Assignments A NATURAL LEFT JOIN (SELECT * FROM Assignment_Settings
>WHERE Setting='Status') ASet
>WHERE A.User_ID IS NOT NULL
> AND ASet.Assignment_ID IS NULL
>GROUP BY User_ID;
"ASet.Assignment_ID IS NULL" and "value IS NULL" as you had in your
original post don't necessarily result in the same set of rows.
SELECT DISTINCT a.User_ID
FROM Assignments a
LEFT JOIN Assignment_Settings s
ON (a.Assignment_ID=s.Assignment_ID
AND s.Setting='Status')
WHERE a.User_ID IS NOT NULL
AND s.Value IS NULL;
Note how the join condition can contain subexpressions that only depend
on columns from one table.
BTW,
|neo=# \d assignment_settings
| [...]
| setting | character varying(250) | not null
| [...]
|Indexes:
| [...]
| "assignment_settings_assignment_id_setting" unique, btree (assignment_id,
setting)
storing the setting names in their own table and referencing them by id
might speed up some queries (and slow down others). Certainly worth a
try ...
Servus
Manfred
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
