Father, Gamer and Application developer. So many hats so many opinions and thoughts.
Inserting Multiple Rows based on Subquery Exclusion
Published on December 29, 2010 By Shalkto In Pure Technology

I work for a company that has offices spread out in various locations throughout North America, the team I work on is called the Woodlands Information Management Team or WIM. Our role is to be the stewards for data management throughout the company and oversee the standards and implementation of day to day applications and reports that the company needs to run and meet it's obligations.

The other day i was looking through the list of outstanding tickets and I seen this one that required me to updated some views for a regional office. The problem was since I am still new I don't have all the regional applications setup yet. Our team has 2 application developers on it me and another lady who has been doing this job for a while now. Part of my role was to take over a large portion of her existing maintenance responsibilities so she could focus on new applications in development.

So I needed access to the same applications as her with the same permissions. The problem was it was the holidays and she was off and rather than disturb her on her day off I wanted to use this as an exercise to dust off my SQL skills which I felt were quite rusty. What follows is the way I found to meet my goals and still do it in the most flexilbe and efficient way possible.

 We have 4 tables that control users' access to the web applications and they are, unoriganally, named USER, APPLICATION,OPERATION, and APPL_USER. My first step was to get a list of all the applications she had access to. A user has a unique combination of USER_ID,OPER_ID,APP_ID, to determine the prviliges. Its a simple system which is nice.

Code: sql
  1. SELECT * FROM APPL_USER WHERE USER_ID ='AAAAAA'


So this returned 153 rows contrasting against mine of 10 rows.

Code: sql
  1. SELECT * FROM APPL_USER WHERE USER_ID ='BBBBBB'


So I could have simply done a dump of my access and done a batch insert of her records changing her ID to mine but I wanted this to be an exercise for me and I also wanted flexibility incase i had to do something similar in the future.

So i decided to use a left outer join to return all the rows of hers that overlapped with mine like so.

Code: sql
  1. /* Formatted on 12/29/2010 11:03:54 AM (QP5 v5.163.1008.3004) Author: Gary Townsend */
  2. SELECT a.APP_ID,
  3.        a.USER_ID,
  4.        a.OPER_ID,
  5.        a.DEFAULT_OPER,
  6.        a.USER_SECURITY_LEVEL,
  7.        a.ADMIN_IND,
  8.        a.UDF_NUM_1,
  9.        b.USER_ID
  10.   FROM    APPL_USER a
  11.        LEFT JOIN
  12.           (SELECT APP_ID,
  13.                   USER_ID,
  14.                   OPER_ID,
  15.                   DEFAULT_OPER,
  16.                   USER_SECURITY_LEVEL,
  17.                   ADMIN_IND,
  18.                   UDF_NUM_1
  19.              FROM APPL_USER
  20.             WHERE USER_ID = 'BBBBBB') b
  21.        ON a.OPER_ID = b.OPER_ID AND a.APP_ID = b.APP_ID
  22.  WHERE a.USER_ID = 'AAAAAA' AND b.USER_ID;


By using the sub query here i can create an outer join that will return all her rows in conjuction with mine, the thing to note here is that the column b.USER_ID is NULL wherever i do NOT have a matching application setup. So to get all the application setups I don't have I simply stuck an IS NULL on the selection criteria in the WHERE clause.

Code: sql
  1. /* Formatted on 12/29/2010 11:03:54 AM (QP5 v5.163.1008.3004) Author: Gary Townsend */
  2. SELECT a.APP_ID,
  3.        a.USER_ID,
  4.        a.OPER_ID,
  5.        a.DEFAULT_OPER,
  6.        a.USER_SECURITY_LEVEL,
  7.        a.ADMIN_IND,
  8.        a.UDF_NUM_1
  9.   FROM    APPL_USER a
  10.        LEFT JOIN
  11.           (SELECT APP_ID,
  12.                   USER_ID,
  13.                   OPER_ID,
  14.                   DEFAULT_OPER,
  15.                   USER_SECURITY_LEVEL,
  16.                   ADMIN_IND,
  17.                   UDF_NUM_1
  18.              FROM APPL_USER
  19.             WHERE USER_ID = 'BBBBBB') b
  20.        ON a.OPER_ID = b.OPER_ID AND a.APP_ID = b.APP_ID
  21.  WHERE a.USER_ID = 'AAAAAA' AND b.USER_ID IS NULL;


This now gives me only the rows where we don't overlap so now all i have to do is a batch insert like so

Code: sql
  1. /* Formatted on 12/29/2010 11:03:54 AM (QP5 v5.163.1008.3004) Author: Gary Townsend */
  2. INSERT INTO APPL_USER(APP_ID,USER_ID,OPER_ID,DEFAULT_OPER,USER_SECURITY_LEVEL,ADMIN_IND,UDF_NUM_1)
  3. SELECT a.APP_ID,
  4.        'BBBBBB' USER_ID,
  5.        a.OPER_ID,
  6.        a.DEFAULT_OPER,
  7.        a.USER_SECURITY_LEVEL,
  8.        a.ADMIN_IND,
  9.        a.UDF_NUM_1
  10.   FROM    APPL_USER a
  11.        LEFT JOIN
  12.           (SELECT APP_ID,
  13.                   USER_ID,
  14.                   OPER_ID,
  15.                   DEFAULT_OPER,
  16.                   USER_SECURITY_LEVEL,
  17.                   ADMIN_IND,
  18.                   UDF_NUM_1
  19.              FROM APPL_USER
  20.             WHERE USER_ID = 'BBBBBB') b
  21.        ON a.OPER_ID = b.OPER_ID AND a.APP_ID = b.APP_ID
  22.  WHERE a.USER_ID = 'AAAAAA' AND b.USER_ID IS NULL;


So hopefully this might help someone who may face the same problem in the future.


Comments
No one has commented on this article. Be the first!