1

I am trying to produce a list of user that have access to particular locations. The table in my database that records locations users have access to is backwards in my opinion. It list the location the users can not access. I have tried not in several different ways without any luck. The query just returns a blank results set. I've tried find something on here to help me and googled it but this has not helped...I always get blank results screen(nothing returned). I think the issue lies with the USER_LOCATION_EXCLUSION having several entries for each USER_ID for each corresponding location. I did not create this table structure but inherited it.

Scenario

Tables                  Columns
====================================================
APP_USER                USER_ID, USER_NAME    
LOCATION                LOCATION_ID, LOCATION_NAME    
USER_LOCATION_EXCLUSION USER_ID, LOCATION_ID

APP_USER and LOCATION tables both have IDs that are unique. These IDs are both used in USER_LOCATION_EXCLUSION (list locations users can not access) and can be used many times in this table according to the users access. I would like to produce report that has the USER_NAME and LOCATION_NAME they have access to.

Improve this question
2
  • I am new to sql so please be kind... Commented Nov 13, 2012 at 16:32
  • Learn the formatting options so your question is more clear. You will receive more help. Commented Nov 13, 2012 at 16:33

2 Answers 2

Reset to default
1

What it sounds like you want is a Cartesian result LESS the exclusions... ie: for each user, assume giving them EVERY location access, then find out what they are excluded from...

select
      PreQuery.User_ID,
      PreQuery.User_Name,
      PreQuery.Location_Name
   from
      ( select
               AU.User_ID,
               AU.User_Name,
               L.Location_ID,
               L.Location_Name
            from
               App_User AU,
               Location L ) PreQuery
         LEFT JOIN USER_LOCATION_EXCLUSION ULE
             on PreQuery.User_ID = ULE.User_ID
             AND PreQuery.LOCATION_ID = ULE.LOCATION_ID
       where
          ULE.Location_ID = NULL

By doing a LEFT JOIN to the exclusion table, every record will ATTEMPT to be joined to the exclusion list... So, when it DOES find a match, that location ID will exist... when it does NOT exist, it will be null (satisfying your NOT Excluded from)

This also eliminates sub-select WHERE clause tested for every user / location

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

DRapp this failed with error...Unknown column 'AU.USER_ID' in 'on clause' ...If I run the statement up to the Left Join it returns all the information so the syntax and naming conventions til there are correct. fthiella's response gave me the results I was looking for...
@user1820795, I know you accepted other answer, but I revised mine so it would work... I did a "prequery" of all user/locations first, THEN left-joined for the Exclusion list. It should work for you now.
DRapp this produced an error as well...I add L.Location_ID to the prequery as it was called out in the error and the query executed but returned no results....this is very similar to what I was doing but I had the prequery in the where portion of the syntax without the null parameter and received the same result. The reason for this I believe is the ULE table only has entries for locations that should not be included. It does not list all locations. Something like that would make more sense and make the query a bit easier :) ....I do thank you for your time and I did learn something!
@trying2getitdone, sorry, not as focused as normal... Anyhow, if I had mysql direct, I would have had it. I did not include the location ID as part of the prequery... so without it, there was no Location_ID column to match the LEFT JOIN qualification. I obviously want a final query working than left hanging.
0

I would use something like this. To obtain all combinations of locations-users you could use a Select query with no joins:

Select *
From Location, App_User

but from here you have to exclude combinations of locations-users that are included in user_location_list table. This can solve your problem:

Select
  Location.*,
  App_User.*
From App_USer, Location
Where
  Not Exists (select * from user_location_exclusion
              where user_location_exclusion.user_id=app_user.user_id
              and user_location_exclusion.location_id=location.location_id)

This shows every location for every user that has not been excluded.

Improve this answer

1 Comment

This worked brilliantly...I was trying to join the tables in the sub query select statement. When I ran the select statement by its self it gave me the information in the table so I thought simply adding the not it would give me the data that was not present.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.