using subselects
Hello everyone -
I'm moving from MySQL to Postgres and I am in a situation where I am
trying to use subselects properly.
I have a table of projects, users, and user_projects. The table
user_projects creates a many-to-many relationship between users and
projects.
I'm creating a select list on a web form for adding a new user to a
project. I want to select users from the user table, who aren't
already listed in the join table under that project.
Here's my select:
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!
I am certain that I am not the $current_user_id. If I run this query:
SELECT * FROM users
WHERE user_id <> $current_user_id
I get all the user records besides myself.
What am I doing wrong?
lawpoop@gmail.com wrote:
I'm creating a select list on a web form for adding a new user to a
project. I want to select users from the user table, who aren't
already listed in the join table under that project.Here's my select:
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!
I suppose you meant to use: AND user_id NOT IN (SELECT user_id FROM ...)
Regards,
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //