Make sure ID exists in table BUT NOT another? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Make sure ID exists in table BUT NOT another?

Author

Topic

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-02-02 : 12:42:36

I have a query, I need to check that a user id is inside one table which I'm joining to retrieve info from another table, but I also need to check that the userid DOES NOT exist in a third table. Example:

SELECT subscribers.userid, users.emailaddress
FROM subscribers, users
WHERE subscribers.userid = users.userid

I have a third table titled email_preferences. I want to make sure that the user id IS NOT listed in that table. How can i conform the query? Is it possible?
Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 12:49:31

[code]
SELECT subscribers.userid, users.emailaddress
FROM subscribers, users
WHERE subscribers.userid = users.userid
AND NOT EXISTS(SELECT 1 FROM email_preferences WHERE userid=users.userid)
[/code]

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-02-02 : 12:55:44

Not sure if this is correct because I didn't test it, but give it a shot.

SELECT s.userid, u.emailaddress
FROM subscribers s
JOIN users u ON s.userid = u.userid
WHERE EXISTS(
SELECT * FROM u WHERE s.userid = u.userid
)
AND NOT EXISTS(
SELECT * FROM email_preferences e WHERE e.userid = s.userid
)

Some days you're the dog, and some days you're the fire hydrant.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 12:58:08

quote:
Originally posted by Skorch

Not sure if this is correct because I didn't test it, but give it a shot.

SELECT s.userid, u.emailaddress
FROM subscribers s
JOIN users u ON s.userid = u.userid
WHERE EXISTS(
SELECT * FROM u WHERE s.userid = u.userid
)
AND NOT EXISTS(
SELECT * FROM email_preferences e WHERE e.userid = s.userid
)

Some days you're the dog, and some days you're the fire hydrant.

the first EXISTS is redundant as that condition is already covered by JOIN

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-02-02 : 13:08:02

Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 13:11:56

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources