Query - SubQuery
Posted on 2011-04-20
I need help with a subquery. I have an application that has 2 tables. One is the user table and the other is the permission table. If the user doesn't exists in the permission table, they have access to everything. I need to create a script that would disable access to a particular application call Vendor. Only way to do this is create an insert that takes from a user I create call "NOACCESS" that would insert it to all users in the permission table. It would be easy if all user exists there, but like what I said if they have access, there's no entry.
So using my base "NOACCESS", I want to insert this for all users. I query the USER table and use their name as the user_Id. I want to insert this entry to all users in the permission table. Not sure how to go about doing that.
INSERT INTO PERMISSION(USER_ID, ACCESS_LEVEL)
SELECT (SELECT DISTINCT NAME FROM USER) AS USER_ID, ACCESS_LEVEL
WHERE USER_ID = 'NOACCESS' AND PROGRAM_ID = 'VENDOR'
So the "N" is no access and the user_id should come from USER table. I can't do a join with USER and PERMISSION table due to some user's don't exists in the Permission table. So I just need to do a mass insert for all users. Any ideas?