How to exclude certain users that are listed twice

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

General SQL Server Forums

New to SQL Server Programming

How to exclude certain users that are listed twice

Author

Topic

bo0ga
Starting Member

7 Posts

Posted - 2012-09-18 : 10:53:58

I basically have a list of about 400 users and need to assign them to 1 of 4 supervisors. The supervisors themselves are also listed in the users which I need to exclude from showing up on my page. There is a second table that shows user role ID's (Where most members are 1-3 and supervisors are 4) - so it would be easy enough to just say "Where RoleID != 4" on my select statement.

Here's the problem though. The supervisors are listed twice with separate user ID's. The first time they are listed with a certain ID, they have 4 as their RoleID in which I can identify them as supervisors. But then they are listed again under another UserId with 3 as their RoleID. How can I remove them twice? I can't individually identify the supervisors and say something like "Where Name !=" because this is only a model for now, and the real data will be coming soon. So I don't know how many supervisors there will be.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 11:21:22

you can do another join on table with condition name=sup.name and roleID=3 to exclude instances of supervisors with roleid 3

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

bo0ga
Starting Member

7 Posts

Posted - 2012-09-18 : 11:28:45

I'm confused about how to do that. Could you help me out? Here's what I have so far.

SELECT B.[UserId], (B.FirstName + ' ' + B.LastName) as Name, B.[ReportToId], (Select B.[FirstName] + ' ' + B.[LastName] from [User] B where B.UserID = B.ReportToID) as SupervisorName
FROM [User] B, [UserRole] A
WHERE B.UserId = A.UserId AND A.RoleId != 4 AND ????
ORDER BY [UserId]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 11:40:45

[code]
SELECT B.[UserId], (B.FirstName + ' ' + B.LastName) as Name, B.[ReportToId]
INTO #Employees
FROM [User] B
INNER JOIN [UserRole] A
ON B.UserId = A.UserId
AND A.RoleId != 4

SELECT e.*,
C.[FirstName] + ' ' + C.[LastName] as SupervisorName
FROM #Employees e
INNER JOIN [User] C
ON C.UserID = e.ReportToID
LEFT JOIN #Employees e1
ON e1.UserID = C.UserID
WHERE e1.UserID IS NULL
ORDER BY e.[UserId]
[/code]

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

bo0ga
Starting Member

7 Posts

Posted - 2012-09-18 : 13:06:59

It's not working, nothing comes up

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 13:10:20

first try this and see if it returns intended result


SELECT B.[UserId], (B.FirstName + ' ' + B.LastName) as Name, B.[ReportToId]
INTO #Employees
FROM [User] B
INNER JOIN [UserRole] A 
ON B.UserId = A.UserId 
AND A.RoleId != 4 


SELECT e.*,
C.[FirstName] + ' ' + C.[LastName] as SupervisorName 
FROM #Employees e
INNER JOIN [User] C
ON C.UserID = e.ReportToID
ORDER BY e.[UserId]

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

bo0ga
Starting Member

7 Posts

Posted - 2012-09-18 : 14:44:12

no both queries don't return anything

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 14:49:37

then i'm sure data is not way you specified

show some sample data from your tables if you need more help on this

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

bo0ga
Starting Member

7 Posts

Posted - 2012-09-18 : 15:45:09

I've only left out some unimportant column names. Would that mess it up?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 15:51:12

i'm not telling on columns, i'm telling about actual data

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

bo0ga
Starting Member

7 Posts

Posted - 2012-09-18 : 16:10:37

Okay I see now that I explained it wrong. The users are listed twice in the same table with the SAME user ID. The first instance they have a role ID of 3 and the second time they have a role ID of 4, but both times, they have the same UserID. There is also a third column in the UserRole table called UserRoleID. The 2nd instance of the 4 supervisors do not show up on my table (which is a good thing) since the userID ends before the last 4 UserRoleIDs. But I still need to get rid of the 1st instance where they have a RoleID of 3. Here's a pic of the end of the UserRole table.

bo0ga
Starting Member

7 Posts

Posted - 2012-09-18 : 16:11:43

So if you were to scroll up on that table, you would see UserId's 198, 195, 191, and 179 listed again with RoleID of 3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 16:14:36

i cant scroll up on static image. either post full table or just take subset of 10 persons with supervisors and post their data

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources