Query help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Query help

  1. #1
    Join Date
    Jul 2012
    Posts
    4

    Query help

    Code:
    I have 2 tables APPS and ACCESS
    
    APPS
    ------------------
    APP_ID
    1
    2
    3
    
    ACCESS
    ------------------
    APP_ID    USER_ID
    1            bosco123
    
    If a record exists for an app in the ACCESS table then it only can be
    displayed to that user.  If no records exist for an app in the ACCESS
    table then it is free to be shown to anyone.
    
    Use Case
    bosco123 logs in.  He should see all apps (1,2 and 3).  someguy456 logs in
    and he should only see 2 and 3.
    
    Seeking help with this query.  It seems simple but I have been struggling.
    
    Thanks

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by bosco123 View Post
    Code:
    I have 2 tables APPS and ACCESS
    
    APPS
    ------------------
    APP_ID
    1
    2
    3
    
    ACCESS
    ------------------
    APP_ID    USER_ID
    1            bosco123
    
    If a record exists for an app in the ACCESS table then it only can be
    displayed to that user.  If no records exist for an app in the ACCESS
    table then it is free to be shown to anyone.
    
    Use Case
    bosco123 logs in.  He should see all apps (1,2 and 3).  someguy456 logs in
    and he should only see 2 and 3.
    
    Seeking help with this query.  It seems simple but I have been struggling.
    Please clarify... specifications and case study are contradictory, according to specifications and supplied data bosco123 should have access only to app=1 while shomeguy456 should have access to all apps.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jul 2012
    Posts
    4
    Hii,
    2 and 3 are unrestricted apps. bosco123 can see these as well. Only when there is a record in ACCESS is an app restricted.
    Thanks

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by bosco123 View Post
    2 and 3 are unrestricted apps. bosco123 can see these as well. Only when there is a record in ACCESS is an app restricted.
    Got it.
    ...and the question is?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Jul 2012
    Posts
    4
    Seeking a query that would return APPS.APP_IDs 1,2,3 if I was to bosco123 and APPS.APP_IDs 2,3 if I was someguy456 (or anyone else) ?

  6. #6
    Join Date
    Jul 2012
    Posts
    4
    Hi PAVB,
    Not sure if this is the most efficient way but I believe it does what I want. Gets all the apps in APPS that are not in ACCESS. This gives all the apps that are available to everyone. Then I do a UNION to get all the apps that are additionally available for your USER_ID. The combined result is all the apps that are available for your particular USER_ID.

    Example:
    SELECT A.APP_ID FROM APPS A
    WHERE NOT EXISTS (SELECT B.APP_ID FROM ACCESS B WHERE A.APP_ID = B.APP_ID)
    UNION
    SELECT DISTINCT B.APP_ID FROM ACCESS B WHERE B.USER_ID= 'bosco123'

    Let me know what you think.

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by bosco123 View Post
    Hii,
    2 and 3 are unrestricted apps. bosco123 can see these as well. Only when there is a record in ACCESS is an app restricted.
    I would add two rows to ACCESS table showing that applications 2 and 3 are unrestricted then your tables should look like...

    Code:
    APPS
    ------------------
    APP_ID
    1
    2
    3
    
    ACCESS
    ------------------
    APP_ID    USER_ID
    1            bosco123
    2            *
    3            *
    ...the you can do:

    Code:
    select  
            a.app_id
    from
            apps a,
            access b
    where
            a.app_id = b.app_id
    and    (b.user_id = 'bosco123'
    or      b.user_id = '*');
    Hope this helps.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Jul 2012
    Posts
    4
    A viable solution. Something to consider Paul. Thank you for the input and sense of community. I really do appreciate it!

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by bosco123 View Post
    A viable solution. Something to consider Paul. Thank you for the input and sense of community. I really do appreciate it!
    You are most welcome - glad you liked it.

    Rationale behind adding those two rows letting the database know that apps 2 and 3 are unrestricted is that a database structure and data should be a picture of reality then the "unrestrictecness" of those has to be reflected in the data.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width