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.

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



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.



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.



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



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