A trick with SQL

Hi people - need help with a little thing on SQL...

I have a function that returns a table of values where one row is called 'UserID' - lets call this table 'x'

I have a another table with a row called 'UserID' in it - lets call this table 'y'

I want an SQL statement that achieves:

Select everything from table x (the table that was generated by the function) where the UserID is not in any row of table y.

Anyone think they can help?

Regards,

Will

SELECT * FROM table_x WHERE UserID NOT IN (SELECT UserID FROM table_y)

or

SELECT * FROM table_x WHERE UserID NOT EXISTS (SELECT 1 FROM table_y WHERE table_y.UserID=table_x.UserID)

|||

This example:

 SELECT t1.* FROM t1 WHERENOT EXISTS (SELECT *FROM t2WHERE t1.id = t2.t1id)

Came from this page:http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

|||

Thanks Guys

A question on subquery return

Here is subquery for a table, called pStatus

pStatus
userid
status

select 1 from pStatus where userid='Robert' and status='p'

How to modify this query so that it also return true if there is't an entry for userid 'Robert'?

Thanks,

vHow about:

where (not exists (select 1 from pStatus where userid='Robert')
or exists (select 1 from pStatus where userid='Robert' and status='p'))|||Thanks Tony.

That is quick and sharp one.

v.|||When I posted this question, I was thinking about using only one "select". The subquery is a small section of a long query with near 1,700 characters. And there are 10 similar subqueries in the query statement needed to have the same change. After running the planner, the performane doesn't seem too bad.

Nested Loop (cost=1.16..33.58 rows=1 width=398) (actual time=2.00..2.00 rows=0 loops=1)

...
Total runtime: 7.00 msec

Only few data at this moment.

v.