Find Duplicate Set
-
I have a table called RolePrivlidges the contains two columns: RoleID and PrivID. Both columns are ints. I need a way of determining if the new set of PrivIDs that I am about to insert already exist exclusively.
For example, say I have a RoleID of 1 that has PrivIDs of 4 and 6. Now I have a new RoleID 7 that I want to insert with PrivIDs of 4 and 6. I need to be able to detect RoleID of 1.
Here is the Kicker, if I have a RoleID of 4 that have PrivIDs of 4, 6, and 7; I do not want to detect that one when I pass in a new RoleID 7 that I want to insert with PrivIDs of 4 and 6.
PLease let me know if you can help.
Thanks!
Don
-
Are you trying to ensure that a privId that you are about to associate with a roleID is not used by a different roleID? To do so you could do something like:
IF NOT EXISTS (Select PrivID FROM RolePriviledges WHERE PrivID = @PrivID)
BEGIN
INSERT INTO RolePriviledges (PrivID, ROleID) VALUES (@PrivID, @RoleID)
END
-
Almost, the problem is I have a set of PrivIDs that can not match exactly another set.
For example, it is bad if the new RoleID passed in has PrivIDs of 1,2,3 and there is a RoleID that already has 1,2,3 and only 1,2,3 for PrivIDs.
It is ok if the new RoleID passed in has PrivIDs of 1,2,3 and there is already a RoleID that has 1,2,3,4.
It is the exact set here that makes the differance. That's what giving me fits. I want to avoid using a cursor if I can, but I am not seeing any other choices.
-
OK, so you are trying to make sure that no two roles have the same priviledges. If you inserted your new priviledges into a tmp table you could do something like:
DECLARE @NewRolePrivCount int
SELECT @NewRolePrivCount = COUNT(*) FROM tmp
Select pr.RoleID
FROM tbl_Privrole pr FULL join tmp ON pr.PrivID = tmp.PrivID
GROUP BY pr.ROLEID
HAVING @NewRolePrivCount = COunt(*) AND @NewRolePrivCount = SUM(CASE WHEN pr.PrivID = tmp.PrivID THEN 1 ELSE 0 END)
That would give you the RoleIDs that have the same priviledges as the ones in the temporary one. You could then use IF EXISTS or whatever else you need to do from there.
Olja
-
Olja,
Many huge props to you! That got it. I don't know if I ever would have gotten that.
Thank you so much!
Don
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply