Re: subselect syntax

Steve Lefevre <lefevre(dot)10(at)osu(dot)edu> writes:
> SELECT * FROM users
> WHERE user_id <> $current_user_id
> AND user_id <> ( SELECT user_id FROM user_projects WHERE project_id =
> $project_id )

> This query returns no rows, even on projects that have no records in
> the user_projects table!

Well, that's not too surprising --- the subselect would deliver a NULL
result, and "user_id <> NULL" can't succeed (it'll always give NULL).

Perhaps what you want is

SELECT * FROM users
WHERE user_id <> $current_user_id
AND user_id NOT IN ( SELECT user_id FROM user_projects WHERE project_id =
$project_id )

although this has its own set of gotchas --- if you have any NULL
user_id entries in user_projects, it'll fail.

regards, tom lane