Re: subselect syntax - Mailing list pgsql-novice

From Tom Lane
Subject Re: subselect syntax
Date
Msg-id 16808.1181171983@sss.pgh.pa.us
Whole thread Raw
In response to subselect syntax  (Steve Lefevre <lefevre.10@osu.edu>)
List pgsql-novice
Steve Lefevre <lefevre.10@osu.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

pgsql-novice by date:

Previous
From: "Phillip Smith"
Date:
Subject: Re: subselect syntax
Next
From: Michael Glaesemann
Date:
Subject: Re: subselect syntax