If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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

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.

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.

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.

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.

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!

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.

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.