StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPicking the most specific result from a SQL result set
text
Body
copied!<p>Below is a simplifed DDL and DML to represent something I've been burning serious amounts of brain matter over. Long timer reader, first time poster and hopefully not breaking any SO etiquette or using too much ink in this post.</p> <p>There are resources that can be turned on or off (permitted) for certain units within the business organization. I've taken a shortcut below and just defined the Sections table, but there would be a Companies and Divisions table as well.</p> <p>I can set permissions for a resource using any combination of Company, Division and Section.</p> <p>A permission set for a Section only would outrank a permission set for just a Division or a Company.</p> <p>Setting a permission to Y with NULL for Company, Division and Section means that if there isn't a permission specific to that business unit, then they will get access to the resource based on this "default" value.</p> <p>At the moment, I am finding the most relevant permission for a business unit by doing multiple SELECTs with the most specific WHERE clause first (looking for ResourcePermission with Company, Division and Section equal to that of the supplied business unit) to the least specific (NULL for all three). Eight SELECTs altogether.</p> <p>If, later on, there are more business levels to be added (Department, UserGroup...), the SELECTs take on the breeding habits of rabbits.</p> <p>Is there a better way to achieve this in SQL or is it better suited to carry this out in procedural code.</p> <p>Running the final SELECT below will give you 9 permissions for resources. I just want the three that are most specific to the business unit specified.</p> <pre><code>CREATE TABLE Resources ( ResourceID varchar(20) NOT NULL PRIMARY KEY NONCLUSTERED, ResourceName varchar(100) NOT NULL) GO CREATE TABLE ResourcePermissions ( PermissionID int identity(1,1) PRIMARY KEY NONCLUSTERED, ResourceID varchar(20) CONSTRAINT [FK_Resources] FOREIGN KEY REFERENCES Resources(ResourceID), Company varchar(10) NULL, Division varchar(10) NULL, Section varchar(20) NULL, Permitted char(1) NOT NULL) GO CREATE TABLE Sections ( Company varchar(10) NOT NULL, Division varchar(10) NOT NULL, Section varchar(20) NOT NULL, SectionName varchar(50) NOT NULL, CONSTRAINT PK_Sections PRIMARY KEY (Company, Division, Section) ) GO INSERT INTO Sections VALUES('Company 1','Division A','Red Section','Redskins') INSERT INTO Sections VALUES ('Company 1','Division A','Blue Section','Bluejays') INSERT INTO Sections VALUES ('Company 1','Division B','Red Section','Redskins') INSERT INTO Sections VALUES ('Company 1','Division B','Blue Section','Bluejays') INSERT INTO Sections VALUES ('Company 1','Division C','Red Section','Redskins') INSERT INTO Sections VALUES ('Company 1','Division C','Blue Section','Bluejays') INSERT INTO Sections VALUES('Company 2','Division A','Red Section','Redskins') INSERT INTO Sections VALUES ('Company 2','Division A','Blue Section','Bluejays') INSERT INTO Sections VALUES ('Company 2','Division B','Red Section','Redskins') INSERT INTO Sections VALUES ('Company 2','Division B','Blue Section','Bluejays') INSERT INTO Sections VALUES ('Company 2','Division C','Red Section','Redskins') INSERT INTO Sections VALUES ('Company 2','Division C','Blue Section','Bluejays') INSERT INTO Resources VALUES('Irish','Irish Resource') INSERT INTO Resources VALUES('English','English Resource') INSERT INTO Resources VALUES('French','French Resource') INSERT INTO ResourcePermissions VALUES('Irish', NULL, NULL, NULL, 'Y') INSERT INTO ResourcePermissions VALUES('Irish', NULL, NULL, 'Blue Section', 'N') INSERT INTO ResourcePermissions VALUES('Irish', NULL, 'Division A', 'Blue Section', 'N') INSERT INTO ResourcePermissions VALUES('Irish', 'Company 1', 'Division A', NULL, 'N') INSERT INTO ResourcePermissions VALUES('French', NULL, 'Division B', 'Blue Section', 'Y') INSERT INTO ResourcePermissions VALUES('French', 'Company 2', NULL, 'Blue Section', 'N') INSERT INTO ResourcePermissions VALUES('French', 'Company 1', NULL, 'Blue Section', 'Y') INSERT INTO ResourcePermissions VALUES('French', NULL, NULL, 'Blue Section', 'Y') INSERT INTO ResourcePermissions VALUES('French', NULL, 'Division B', 'Red Section', 'N') INSERT INTO ResourcePermissions VALUES('French', NULL, 'Division C', 'Red Section', 'Y') INSERT INTO ResourcePermissions VALUES('English', NULL, 'Division B', 'Blue Section', 'Y') INSERT INTO ResourcePermissions VALUES('English', 'Company 2', NULL, 'Blue Section', 'N') INSERT INTO ResourcePermissions VALUES('English', NULL, 'Division A', 'Blue Section', 'N') INSERT INTO ResourcePermissions VALUES('English', NULL, NULL, 'Blue Section', 'N') INSERT INTO ResourcePermissions VALUES('English', 'Company 1', 'Division A', 'Blue Section', 'Y') </code></pre> <h3>Query:</h3> <pre><code> SELECT ResourceID, Company, Division, Section, Permitted FROM ResourcePermissions WHERE (Company = 'Company 1' OR Company IS NULL) AND (Division = 'Division A' OR Division IS NULL) AND (Section = 'Blue Section' OR Section IS NULL) ORDER BY ResourceID </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload