NOT EXISTS question - 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 2008 Forums

Transact-SQL (2008)

NOT EXISTS question

Author

Topic

Spica66
Starting Member

23 Posts

Posted - 2012-08-21 : 10:56:54

I have a 3 tables.

#userInfo#
[u_ID][NetworkAlias]
1.....david.james
2.....mary.jones
3.....john.smith

#windowsUsers#
[userID][windowsUserName]
1.......john.smith
2.......david.james
3.......mary.jones

#brandUsers#
[userID][Brands]
1.......BC,CS
7.......DD
8.......BH

I want to take the list of all users from userInfo, get their userID, and return the networkAlias if they are NOT already included in brandUsers table.

When I run this query:

SELECT w.userID
,w.WindowsUserName

FROM dbo.UserInfo u

RIGHT JOIN windowsUsers w ON u.networkAlias=w.windowsUserName

RIGHT JOIN brandUsers b on b.userID=w.userID

I get only david.james. That is the expected result.

So I thought I would use that in a Not Exists clause [give me all of the networkAlias that are not in the query - in this case, everyone except david.james]:

SELECT DISTINCT u.networkAlias

From dbo.UserInfo u

WHERE

NOT EXISTS (SELECT w.userID
,w.WindowsUserName

FROM dbo.UserInfo u

RIGHT JOIN windowsUsers w ON u.networkAlias=w.windowsUserName

RIGHT JOIN brandUsers b on b.userID=w.userID)

But I get a blank result. Am I totally missing the boat on using NOT EXISTS?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-21 : 11:00:58

you didnt link it to main query


SELECT u.networkAlias

From dbo.UserInfo u

WHERE

NOT EXISTS (SELECT 1
FROM dbo.UserInfo u1

INNER JOIN windowsUsers w ON u1.networkAlias=w.windowsUserName

INNER JOIN brandUsers b on b.userID=w.userID
WHERE u1.networkAlias = u.networkAlias)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources