There are times when you need to verify that a given record is associated with all of a given type of records. In this post, I'll be going over the MINUS statement and show you a practical use for this often under used functionality of SQL.
For this example we'll be looking at customer data for an online application. ACME paper in has unleashed a new paper sales website. They allow the employees of the company to which they sale paper to have access to various sections of their website. In order to have more granular control, they control access the sections at both the user level and the company level. In order for a user to have access to a section, they must both have access at the company level and at the user level. Let's take a look at our tables to get a better idea of what we are dealing with.
| ID | COMPANY_NAME |
| 1 | Awesome Corp. |
| ID | USERNAME |
| 1 | jdoe |
| 2 | bsmith |
| ID | SECTION_NAME |
| 1 | Main |
| 2 | Special |
| COMP_ID | USER_ID |
| 1 | 1 |
| 1 | 2 |
| COMP_ID | SECT_ID |
| 1 | 1 |
| 1 | 2 |
| USER_ID | SECT_ID |
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
The first table (COMPANY) shows the companies that ACME does business with. The second table (USER) shows the users that are using the new online sales site. The third table (SECTION) shows the various sections that are available on the online sales site. The fourth table (COMPANYUSERJOIN) links the users to the various companies. The fifth table (COMPANYSECTIONJOIN) gives company access to the various sections. The sixth table (USERSECTIONJOIN) gives user access to the various sections.
Management has requested a report of all of the users that don't have access to all of the sections that their company has access to. In comes MINUS to save the day. Minus will return only the rows that are not in both queries. Just like UNION, the data types in both SQL queries must be the same. Let's take a look at the query that will return the data we are looking for.
SELECT SECTION.ID, USER.USERNAME FROM USER
JOIN COMPANYUSERJOIN ON COMPANYUSERJOIN.USER_ID = USER.ID
JOIN COMPANYSECTIONJOIN ON COMPANYSECTIONJOIN.COMP_ID = COMPANYUSERJOIN.COMP_ID
JOIN SECTION ON SECTION.ID = COMPANYSECTIONJOIN.SECT_ID
MINUS
SELECT SECTION.ID, USER.USERNAME FROM USER
JOIN USERSECTIONJOIN ON USERSECTIONJOIN.USER_ID = USER.ID
Looking at the SQL query above, we can see in the first section that we are selecting the section id and the username by joining the section table through what the corporation will have access to. This will return all of the usernames for the corporations, along with all of the section ids the corporation has access to. Then we have minus before the second part of the query. In the second part of the SQL query, we are returning the section ids and usernames based on what section each user actually has access to.
The minus will compare these two SQL queries and remove duplicate records. This will return two types of records. The first type of record returned will be the sections that a company has access to, but that a given user has not been given access to. The second type of record that will be returned will be sections that a user has access to, but that a given corporation does not. For our exercise, we will assume that a given user can't be given access to a section without the company also having access.
Using the example data above, this query will return a section id of 2 and username of bsmith. This means that bsmith is part of a company that has access to section 2, but bsmith does not have access to this section. We then write up our report for management, cut out of work early, and go grab a cold beer or two with some friends. Thanks to the SQL MINUS query.
0 comments:
Post a Comment