Query help

[bosco123]

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

[PAVB]

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.

[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.
Thanks

[PAVB]

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?

[bosco123]

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) ?

[bosco123]

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.

[PAVB]

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...

APPS
------------------
APP_ID
1
2
3

ACCESS
------------------
APP_ID    USER_ID
1            bosco123
2            *
3            *

...the you can do:

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.

[bosco123]

A viable solution. Something to consider Paul. Thank you for the input and sense of community. I really do appreciate it!

[PAVB]

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.