Where combination of values is NOT IN a table

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)

Where combination of values is NOT IN a table

Author

Topic

charliesharkey
Starting Member

2 Posts

Posted - 2009-03-16 : 05:05:38

Hello community this one has me really stumped and I hope someone can help.

I have a list of workspaces which each have individual administrators.
Some administrators administrate more than one workspace.
Each admin user may also have workspaces which s/he is a member of but doesnt administrate.

SO..

Table A has a list of workspace IDs and Login names of Administrators.

Therefore each row has two fields, WorkID and LoginName.

Table B has a list of users. So each row has WorkID, LoginName, and LastLoginDate.

I need to run a query on lastlogin dates for each workspace's users but disregard where the user is an administrator for THAT workspace.

Therefore I think I need to run a NOT IN, i.e. find last login for users for each workspace but make sure that COMBINATION OF LOGINNAME AND WORKSPACE ID IS "NOT IN" TABLE A.

I know how to run a NOT IN query but only where a single value is NOT IN a set, not where we are looking for two values not to exist as a combination in a table.

i.e.
where TableB.LoginName NOT IN
(select LoginName from TableA)

Is this clear? Can anyone help...???

Thanks in advance
Charlie

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-16 : 06:03:15

try this once,
select workid,loginname,max(lastlogindate) from tableb where loginname not in (select loginname from tablea) group by workid,loginname

select workid,loginname,max(lastlogindate) from tableb t where not exists (select * from tablea where loginname = t.loginname) group by workid,loginname

charliesharkey
Starting Member

2 Posts

Posted - 2009-03-16 : 08:00:27

hey bklr, thanks for this, it got me on the right track.
the full query which did the trick was:

SELECT ComID, LoginName, LastLogin,
FROM tableB AS t
WHERE (NOT EXISTS
(SELECT LoginName
FROM tableA
WHERE (LoginName = t.LoginName) AND (ComID = t.ComID)))

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-16 : 08:03:32

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources