Post reply

Find Duplicate Set

  • March 25, 2005 at 8:03 am

    #185568

    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

  • March 25, 2005 at 5:56 pm

    #549030

    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

  • March 25, 2005 at 6:47 pm

    #549033

    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.

  • March 25, 2005 at 11:10 pm

    #549038

    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

  • March 28, 2005 at 6:05 am

    #549094

    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