-
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
-
 Originally Posted by bosco123
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.
-
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
-
 Originally Posted by bosco123
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.
-
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) ?
-
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.
-
 Originally Posted by bosco123
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.
-
A viable solution. Something to consider Paul. Thank you for the input and sense of community. I really do appreciate it!
-
 Originally Posted by bosco123
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|