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

Re: subselect syntax

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Steve Lefevre <lefevre(dot)10(at)osu(dot)edu>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: subselect syntax
Date: 2007-06-06 23:19:43
Message-ID: 16808.1181171983@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Michael GlaesemannDate: 2007-06-06 23:26:59
Subject: Re: subselect syntax
Previous:From: Phillip SmithDate: 2007-06-06 23:15:03
Subject: Re: subselect syntax

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