?
Solved

Query -  SubQuery

Posted on 2011-04-20
3
Medium Priority
?
217 Views
Last Modified: 2012-05-11
Hello experts,

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.

Example:

INSERT INTO PERMISSION(USER_ID, ACCESS_LEVEL)
SELECT (SELECT DISTINCT NAME FROM USER) AS USER_ID, ACCESS_LEVEL
FROM PERMISSION
WHERE USER_ID = 'NOACCESS' AND PROGRAM_ID = 'VENDOR'

Output:

chook, N
lginsin, N
dolson, N

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?
0
Comment
Question by:holemania
3 Comments
 
LVL 11

Expert Comment

by:brutaldev
ID: 35435528
Just filter the select statement using a sub-select. The code below will give you all users that have permissions for program ID 'Vendor' and the user ID 'NoAccess' which it looks like you were trying to achieve.

 
INSERT INTO PERMISSION (USER_ID, ACCESS_LEVEL)
SELECT DISTINCT NAME, ACCESS_LEVEL
FROM USER
WHERE NAME IN (SELECT USER_ID FROM PERMISSION WHERE USER_ID = 'NOACCESS' PROGRAM_ID = 'VENDOR')

Open in new window

0
 
LVL 33

Accepted Solution

by:
knightEknight earned 2000 total points
ID: 35435551
If I understand your requirements, I think this will do it.  Run the SELECT part first (without the insert) to be sure.

INSERT INTO PERMISSION(USER_ID, ACCESS_LEVEL)
SELECT distinct U.NAME AS USER_ID, P.ACCESS_LEVEL
FROM PERMISSION P
JOIN USER U
   on 1 = 1
WHERE P.USER_ID = 'NOACCESS'
   AND P.PROGRAM_ID = 'VENDOR'
0
 

Author Closing Comment

by:holemania
ID: 35435929
Thank you.  That's what I was after.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question