Select rows that aren't in the current select

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)

Select rows that aren't in the current select

Author

Topic

danielhai
Yak Posting Veteran

50 Posts

Posted - 2002-06-05 : 17:20:48

This seems like it should be simple, but I've been racking my brain trying to figure it out.

I have a table with Rights, Users/Groups, and their relation:


UserGroup

UserID  Type  Name
0       0     Everyone
1       1     dhai
2       2     Administrators

Right

RightID  Name
1        Create
2        Edit
3        Delete

UserGroupRight

UserGroupRightID  UserID  RightID RightAllow
1                 1       1       1
2                 1       2       -1
3                 0       1       -1
4                 2       2       1

now my question is how to get a FULL select of ALL the rights for every user? Basically it would loop through(there's gotta be faster way then looping? So the result set would look like this:


UserID  RightID   RightAllow
0       1         -1
0       2         NULL
0       3         NULL
1       1         1
1       2         -1
1       3         NULL
2       1         NULL
2       2         1

make sense? any help is always greatly appreciated.

Edited by - danielhai on 06/05/2002 17:22:11

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-06-05 : 18:40:15

WOOHOO I got the answer! That was a tough one for me...
That's the first time I've used CROSS JOINS, derived tables, and a FULL JOIN with a predicate. I figured this all out using BOL. :)

CREATE TABLE #UserGroup(UserID int, Type int, Name varchar(50))
INSERT INTO #usergroup(UserID, Type, name) VALUES(0, 0, 'Everyone')
INSERT INTO #usergroup(UserID, Type, name) VALUES(1, 1, 'dhai')
INSERT INTO #usergroup(UserID, Type, name) VALUES(2, 2, 'Administrators')

CREATE TABLE #Right(RightID int, Name varchar(50))
INSERT INTO #Right(RightID, Name) VALUES(1, 'Create')
INSERT INTO #Right(RightID, Name) VALUES(2, 'Edit')
INSERT INTO #Right(RightID, Name) VALUES(3, 'Delete')

CREATE TABLE #UserGroupRight(UGRID int, UserID int, RightID int, RightAllow int)

INSERT INTO #UserGroupRight(UGRID, UserID, RightID, RightAllow) VALUES(1,1,1,1)
INSERT INTO #UserGroupRight(UGRID, UserID, RightID, RightAllow) VALUES(2,1,2,-1)
INSERT INTO #UserGroupRight(UGRID, UserID, RightID, RightAllow) VALUES(3,0,1,-1)
INSERT INTO #UserGroupRight(UGRID, UserID, RightID, RightAllow) VALUES(4,2,2,1)

--SELECT ug.UserID, r.RightID FROM #UserGroup ug CROSS JOIN #Right r ORDER BY ug.UserID, r.RightID

SELECT a.UserID, a.RightID, ugr.RightAllow
FROM (SELECT ug.UserID, r.RightID FROM #UserGroup ug CROSS JOIN #Right r) as a
FULL OUTER JOIN #UserGroupRight ugr on ugr.RightID = a.RightID AND ugr.UserID = a.UserID
ORDER BY a.UserID, a.RightID

DROP TABLE #UserGroupRight
DROP TABLE #Right
DROP TABLE #UserGroup

Edited by - michaelp on 06/05/2002 18:41:57

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-06-05 : 19:21:47

Mike,

Why have a derived table at all? If we lose the derived table we can also replace the FULL OUTER JOIN with a LEFT JOIN. This will replace a Hash Match with a Nested Loop...


SELECT UG.UserID, R.RightID, UGR.RightAllow
FROM #UserGroup UG CROSS JOIN #Right R
LEFT JOIN #UserGroupRight UGR ON UGR.UserID = UG.UserID AND UGR.RightID = R.RightID

Same result....

DavidM

"SQL-3 is an abomination.."

Edited by - byrmol on 06/05/2002 19:22:19

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-06-05 : 19:25:00

Yeah, I should have fixed that.
The Derived table was a work in progress from another way I was trying to solve it. Once I got this solution to work, I needed to get back to work :). I didn't have time to optimise it.

Good catch though!

Michael

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-06-05 : 19:29:19

I know how that feels...

Sometimes you have about 10 different plans to attack a query.. I find I always move to the easiest path first then hopefully come back and "fix" it.

Nice work on the DML and DDL..

If only the orginal posters would do that....

DavidM

"SQL-3 is an abomination.."

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-06-05 : 19:37:51

Yeah I wish we could get them to do that so we don't have to type all that stuff when trying to solve their problems, but oh well.

I know I usually don't post my DML and DDL. I'll have to get into that habit....

Hmmm Maybe I'll write an app that would generate DML and DDL that you could post using Temp tables etc...
Would anyone be interested in such a tool??

Michael

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-06-05 : 20:09:51

The problem with that is what format do we tell the user to use so we can parse it?

We have a hard enough time telling them to use SQL!

DavidM

"SQL-3 is an abomination.."

danielhai
Yak Posting Veteran

50 Posts

Posted - 2002-06-06 : 15:59:13

you know, i knew i had posted the right table for a reason. Sorry guys, but there's a little addition to this. Thanks again for all your help - i've never worked with cross joins before, trying to learn them as much as i can now ... but here's the next question.

Let's say I add an AreaID field to the Right table, and 2 more users that won't have rights inserted into UserGroupRight. Now I want to grab just users that have rights in the usergroupright table, and now based upon an areaid.

Here's how the data looks:


UserGroup

UserID  Type  Name
0       0     Everyone
1       1     dhai
2       0     Administrators
3       1     NotUsed
4       1     NotUsed1

Right

RightID  AreaID   Name
1        1        Create
2        1        Edit
3        1        Delete
4        2        NewRight1
5        2        NewRight2
6        2        NewRight3

UserGroupRight

UserGroupRightID  UserID  RightID RightAllow
1                 1       1       1
2                 1       2       -1
3                 0       1       -1
4                 2       2       1
5                 2       1       -1

Output

UserID  RightID   RightAllow
0       1         -1
0       2         NULL
0       3         NULL
1       1         1
1       2         -1
1       3         NULL
2       1         -1
2       2         1

Here's the DDR:


CREATE TABLE #UserGroup(UserID int, Type int, Name varchar(50)) 
INSERT INTO #usergroup(UserID, Type, name) VALUES(0, 0, 'Everyone') 
INSERT INTO #usergroup(UserID, Type, name) VALUES(1, 1, 'dhai') 
INSERT INTO #usergroup(UserID, Type, name) VALUES(2, 0, 'Administrators') 
INSERT INTO #usergroup(UserID, Type, name) VALUES(3, 0, 'NotUsed') 
INSERT INTO #usergroup(UserID, Type, name) VALUES(4, 0, 'NotUsed1') 

CREATE TABLE #Right(RightID int, AreaID int, Name varchar(50)) 
INSERT INTO #Right(RightID, AreaID, Name) VALUES(1, 1, 'Create') 
INSERT INTO #Right(RightID, AreaID, Name) VALUES(2, 1, 'Edit') 
INSERT INTO #Right(RightID, AreaID, Name) VALUES(3, 1, 'Delete') 
INSERT INTO #Right(RightID, AreaID, Name) VALUES(4, 1, 'NewRight1') 
INSERT INTO #Right(RightID, AreaID, Name) VALUES(5, 1, 'NewRight2') 
INSERT INTO #Right(RightID, AreaID, Name) VALUES(6, 1, 'NewRight3') 

CREATE TABLE #UserGroupRight(UGRID int identity, UserID int, RightID int, RightAllow int) 

INSERT INTO #UserGroupRight(UserID, RightID, RightAllow) VALUES(1,1,1) 
INSERT INTO #UserGroupRight(UserID, RightID, RightAllow) VALUES(1,2,-1) 
INSERT INTO #UserGroupRight(UserID, RightID, RightAllow) VALUES(0,1,-1) 
INSERT INTO #UserGroupRight(UserID, RightID, RightAllow) VALUES(2,2,1) 
INSERT INTO #UserGroupRight(UserID, RightID, RightAllow) VALUES(2,1,-1) 

SELECT UG.UserID, R.RightID, UGR.RightAllow
FROM #UserGroup UG CROSS JOIN #Right R
LEFT JOIN #UserGroupRight UGR ON UGR.UserID = UG.UserID AND UGR.RightID = R.RightID
/*
left join #Right rr
where
	rr.AreaID = 1
*/

DROP TABLE #UserGroupRight 
DROP TABLE #Right 
DROP TABLE #UserGroup

The result set it returns now returns all the users, not just the users that have records within usergroupright. This is a doozy for me, again, thanks for all your help.

Edited by - danielhai on 06/10/2002 14:08:53

danielhai
Yak Posting Veteran

50 Posts

Posted - 2002-06-10 : 17:14:43

well, i found ONE way to do it ... but i know it's not totally optimal ...

select ug.UserID,
ar.RightID,
ugr.RightAllow
from UserGroup ug CROSS JOIN AreaRight ar
LEFT JOIN UserGroupRight ugr ON ugr.UserID = ug.UserID AND ugr.RightID = ar.RightID
where
exists (select * from usergroupright left outer join arearight on ugr.UserID = ug.UserID and ugr.RightID = ar.RightID where userid = ug.userid and ar.AreaID = @AreaID) and
ar.AreaID = @AreaID

thanks again for all your help guys ...

Edited by - danielhai on 06/10/2002 17:20:24

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-06-10 : 17:53:40

danielhai,

You can make yours a little more readable....


DECLARE @AreaID INT
SET @AreaID = 1

SELECT UG.UserID, R.RightID, UGR.RightAllow
FROM #UserGroup UG CROSS JOIN #Right R 
LEFT JOIN #UserGroupRight UGR ON UGR.UserID = UG.UserID AND UGR.RightID = R.RightID
WHERE EXISTS (SELECT 1 FROM #UserGroupRight WHERE UserID = UG.UserID)
AND R.AreaID = @AreaID

HTH

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources