Skip site navigation (1) Skip section navigation (2)

subselect syntax

From: Steve Lefevre <lefevre(dot)10(at)osu(dot)edu>
To: pgsql-novice(at)postgresql(dot)org
Subject: subselect syntax
Date: 2007-06-06 21:39:04
Message-ID: 46672978.2000909@osu.edu (view raw or flat)
Thread:
Lists: pgsql-novice
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?

Responses

pgsql-novice by date

Next:From: Phillip SmithDate: 2007-06-06 23:15:03
Subject: Re: subselect syntax
Previous:From: Tom LaneDate: 2007-06-06 14:45:25
Subject: Re: Installation of postgresql database as root

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group