finding records to insert based on 2 fields

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 2000 Forums

SQL Server Development (2000)

finding records to insert based on 2 fields

Author

Topic

fparker
Starting Member

27 Posts

Posted - 2012-01-05 : 10:50:06

I did some searching around the forums but couldn't find something that matched what I was looking for so here we go.

I have a table where the userid and branch can have multiple objectids. This gives various access in the system. However, I am only interested in userids that have one particular objectid. The problem is complicated by a userid having the more than one branch it can access.

So what I want is all the userids from the securitymain table with the objectid of 40003 by branch.

This gives me all the userids that i need to exclude from my list results:

select userid, branchno from securitymain where objectid = 40003

Now I need to get the userids by branch that are not part of the results of the above.

Does this make sense?

--
f

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-05 : 11:29:11

[code]
select *
from securitymain s
where not exists
(
select 1 from securitymain where objectid = 40003 and userid = s.userid and branchno=s.branchno)
[/code]

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

fparker
Starting Member

27 Posts

Posted - 2012-01-05 : 11:51:19

Thanks for the reply but it is giving me too many rows back.

The data looks like this

userid, branchno, objectid

user1, 001, 40003
user1, 001, 20001
user2, 001, 20001
user2, 001, 20002
user2, 002, 20001
user2, 002, 20002
user3, 002, 20001
user3, 002, 40003
user4, 003, 20001

I don't want user3 or user1 in my final results because they have objectid 40003.

--
f

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-05 : 13:54:45

select *
from securitymain s
where not exists
(
select 1 from securitymain where objectid = 40003 and userid = s.userid)

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

fparker
Starting Member

27 Posts

Posted - 2012-01-05 : 14:25:41

thanks. that gets me to where i want to go today!

--
f

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-06 : 13:56:56

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources