Help With Select Statement

  • I have hopefully a fairly easy question which I think I am just missing something.

    We have a security application that needs to track when a user's access badge has had access removed or added. Now the 3rd party app (which I cannot change their code) handles this from a screen. You can revoke and add access to certain areas of the building in one go and when you hit update the code deletes all of the access and then readds it with the changes.

    IE:) I have a badge that has access to 3 areas, I add 2 areas to the badge, the app will perform 3 delete statements and then 5 insert statements... 3 inserts for the original access and 2 inserts for the new access.

    What I have done is created 2 triggers and 2 tables.

    Tables:

    BADGELINK_ACCESS_ADDED

    BADGELINK_ACCESS_REMOVED

    The delete trigger inserts into the BADGELINK_ACCESS_REMOVED table and the insert trigger inserts into the BADGELINK_ACCESS_ADDED table.

    What I need is a query that will give me just the new access based on the info in those 2 tables.

    Both tables have:

    BADGEKEY -- BADGE NUMBER

    ACCLVLID -- ACCESS AREA ID (Server Room, Cafeteria, etc)

    AUDITDATETIME -- THE DATETIME THE ACCESS WAS GRANTED OR REVOKED

    I have test data in the table with 2 badges, one had 4 areas added and one had one area added.

    BADGELINK_ACCESS_REMOVED

    BADGEKEY ACCLVLID AUDITDATETIME

    10695 10 2012-01-31 11:04:22

    10695 12 2012-01-31 11:04:22

    1356 10 2012-01-31 13:11:09

    1356 17 2012-01-31 13:11:09

    1356 19 2012-01-31 13:11:09

    1356 22 2012-01-31 13:11:09

    1356 34 2012-01-31 13:11:09

    BADGELINK_ACCESS_ADDED

    BADGEKEY ACCLVLID AUDITDATETIME

    10695 10 2012-01-31 11:04:22

    10695 12 2012-01-31 11:04:22

    10695 17 2012-01-31 11:04:22

    10695 19 2012-01-31 11:04:22

    1356 10 2012-01-31 13:11:09

    1356 17 2012-01-31 13:11:09

    1356 19 2012-01-31 13:11:09

    1356 22 2012-01-31 13:11:09

    1356 34 2012-01-31 13:11:09

    1356 47 2012-01-31 13:11:09

    Notice access for badge 10695 areas 17 and 19 added..

    and access for badge 1356 area 47 was added...

    So what I would like to see is 3 rows returned from the BADGELINK_ACCESS_ADDED that do not exist in the REMOVED table...

    I am running:

    SELECT AA.* FROM BADGELINK_ACCESS_ADDED AA

    LEFT JOIN BADGELINK_ACCESS_REMOVED AR

    ON (AA.ACCLVLID = AR.ACCLVLID)

    WHERE AR.BADGEKEY IS NULL

    But I only get the one row from badge 1356 that had one access area added... The other 2 for badge 10695 don't show up.

    Anybody can help me in figuring out what I might be doing wrong? Or if you can come up with a better way of grabbing when access has been added or removed from a certain badge that would be great too.

    Thanks

  • I think I may have just figured it out...

    I need to add the badgekey to the join clause:

    SELECT AA.* FROM BADGELINK_ACCESS_ADDED AA

    LEFT JOIN BADGELINK_ACCESS_REMOVED AR

    ON (AA.ACCLVLID = AR.ACCLVLID

    AND AA.BADGEKEY = AR.BADGEKEY)

    WHERE AR.BADGEKEY IS NULL

    I am going to play around with it... I may need to add the AUDITDATETIME field in at some point and then reverse the query so I can do the same when an access area has been removed from a badge but this is a good start.

  • jamie_collins (1/31/2012)


    I am going to play around with it... I may need to add the AUDITDATETIME field in at some point and then reverse the query so I can do the same when an access area has been removed from a badge but this is a good start.

    Yep, you got it, but you'll want to add in AuditDateTime too to avoid errors when the same user is in the table twice because of further modifications. You could also inverse the query and union it to itself to show removals.

    Btw, if you take a look at the first link in my signature, it'll help you setup your schema/data for next time and more people will be able to help you faster. Remember we're volunteers, the easier you make it on us, the easier it is for us to help. 🙂


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Take a look at EXCEPT which performs a set difference, which is what you are looking for. It makes it much clearer that you are doing a set difference and is much cleaner as the number of fields that your comparing grows larger.

    SELECT AA.ACCLVLID, AA.BADGEKEY, AA.AUDITDATETIME

    FROM BADGELINK_ACCESS_ADDED AA

    EXCEPT

    SELECT AR.ACCLVLID, AR.BADGEKEY, AR.AUDITDATETIME

    FROM BADGELINK_ACCESS_REMOVED AR

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen (2/1/2012)


    Take a look at EXCEPT which performs a set difference, which is what you are looking for. It makes it much clearer that you are doing a set difference and is much cleaner as the number of fields that your comparing grows larger.

    SELECT AA.ACCLVLID, AA.BADGEKEY, AA.AUDITDATETIME

    FROM BADGELINK_ACCESS_ADDED AA

    EXCEPT

    SELECT AR.ACCLVLID, AR.BADGEKEY, AR.AUDITDATETIME

    FROM BADGELINK_ACCESS_REMOVED AR

    Drew

    As long as you have an exact match for the columns being returned from the statement I'll agree with this, otherwise you have to get into subquerying and it's not as optimal.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply