NOT EXISTS problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

NOT EXISTS problem

Author

Topic

sqlpupil
Starting Member

5 Posts

Posted - 2008-01-11 : 16:28:21

The second query below returns 0 records when I add the "NOT EXISTS" clause and there's a least one record from the sub-query; I'd like it to return all of the records that aren't in the first query. Does anybody know how I can get the second query to only return user2IDs that are not in the first query. Thank you.

--QUERY 1
SELECT user2ID
FROM userLink
WHERE user1ID = 2

--QUERY 2
SELECT user2ID
FROM userLink
WHERE user1ID = 6
AND NOT EXISTS (
--Sub query; same as Query 1
SELECT user2ID
FROM userLink
WHERE user1ID = 2
)

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-11 : 16:39:23

[code]
SELECT U1.user2ID
FROM userLink U1
WHERE U1.user1ID = 6
AND NOT EXISTS (
--Sub query; same as Query 1
SELECT U2.user2ID
FROM userLink U2
WHERE U2.user1ID = 2 AND U2.userid = U1.userid
)
[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sqlpupil
Starting Member

5 Posts

Posted - 2008-01-11 : 17:06:13

Thank you Dinakar. It worked after I made the small adjustment of

WHERE U2.user1ID = 2 AND U2.user2id = U1.user2id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources