How to avoid conditional join in order to write a tsql view

Question

I have a scenario where I have Roles, Set, DefaultSet, and CompanySet tables

Roles table

RoleName
---------
Admin
Viewer
User

Set table

Id      Name
---     ----
1       Employee Data
2       Help Desk
3       Comments
4       Interests

DefaultSet table

RoleName   SetId
--------   -----
Admin      1
Admin      2
User       1
User       3

CompanySet table

Company   RoleName   SetId
---------   --------   -----
Acme Inc    Admin      1
Acme Inc    Viewer     1
Bobs Paint  User       2
Dans Disks  Admin      2

This query works but I want to be able to restructure it in a way that it is parameterless('Acme Inc' condition is the param) and can be used in a view.

WITH T1 AS
(
    SELECT Company, RoleName, cs.SetId  FROM Roles r
    LEFT JOIN CompanySet cs on cs.RoleName = r.RoleName
    GROUP BY Company, r.RoleName, cs.SetId
),
T2 AS 
(
    SELECT  RoleName, ds.SetId  FROM Roles r
        INNER JOIN DefaultSet ds on r.RoleName = ds.RoleName
        GROUP BY r.RoleName, ds.SetId

)
SELECT * FROM t2
    LEFT JOIN t1 ON t1.RoleName = t2.RoleName 
                 AND t1.SetId = t2.SetId 
                 AND T1.Company = 'Acme Inc'

Seems like maybe I have to cross join the company on the t2 table so I can match the condition against it. Let me know if there is a way to solve this.

Answer 1

It seems like to me that the query you have could be rewritten as:

SELECT R.RoleName, ds.SetID, --T2 Columns
CS.Company, R.RoleName, cs.SetID --T1 Columns
FROM Roles r
    INNER JOIN DefaultSet ds 
        on r.RoleName = ds.RoleName
    LEFT OUTER JOIN CompanySet CS
        ON CS/RoleName = R.RoleName
WHERE CS.Company = 'Acme Inc'

If that is the case, than you would be able to create a view like:

CREATE VIEW ViewName
AS
BEGIN
    SELECT R.RoleName, 
    ds.SetID,
    CS.Company --, 
    --R.RoleName, --Don't Want To duplicate The Column In The View
    --cs.SetID --Don't Want To duplicate The Column In The View
    FROM Roles r
        INNER JOIN DefaultSet ds 
            on r.RoleName = ds.RoleName
        LEFT OUTER JOIN CompanySet CS
            ON CS/RoleName = R.RoleName
END

Then you would simply be able to query the Company column like normal.

SELECT *
FROM ViewName
WHERE Company = 'Acme Inc'

Hopefully that helps and I didn't miss-read the question you asked.

Answer 2

The problem is basically that I have a table of defaults and a number of company tenants that override the default set items. In other words look to the role in the company set table and if there are features there then use them. So if a row is added to the CompanySet for a role that item overrides the DefaultSet. The other problem is that when I expand the features by joining the actual feature items then I can't compare the items. I am difficulty getting describing my challenges since it is all new to me. This seems like a viable solution so hopefully some of the information can be inferred from the example.

WITH ComFeatures as
(
    SELECT cs.CompanyId, RoleId, FeatureId
    FROM CompanySet cs 
        INNER JOIN SetItem i on i.SetId = cs.SetId
 ),
 ComGroups as
 (
    SELECT CompanyId, RoleId FROM ComFeatures
    GROUP BY CompanyId, RoleId
 ),
 ComRecord as
 (
    SELECT CompanyId, RoleId,
        STUFF((SELECT ', ' +  CAST(FeatureId as nvarchar(20))
           FROM ComFeatures n 
           WHERE n.RoleId = ComGroups.RoleId  AND n.CompanyId = ComGroups.CompanyId
           FOR XML PATH('')), 1, 2, '') ComFeatures
    FROM ComGroups
 ),
 DefFeatures as
 (
    SELECT c.CompanyId, r.RoleId, FeatureId
    FROM aspnet_Roles r 
        LEFT JOIN DefaultSet cs on cs.RoleId = r.RoleId
        CROSS JOIN (SELECT Id AS CompanyId FROM Company) c
        INNER JOIN SetItem i on i.SetId = cs.SetId 
 ),
 DefGroup as 
 (
    SELECT CompanyId, RoleId FROM DefFeatures
    GROUP BY CompanyId, RoleId
),
 DefRecord as
 (
    SELECT CompanyId, RoleId,
        STUFF((SELECT ', ' +  CAST(FeatureId as nvarchar(20))
           FROM DefFeatures n 
           WHERE n.RoleId = DefGroup.RoleId  AND n.CompanyId = DefGroup.CompanyId 
           FOR XML PATH('')), 1, 2, '') DefFeatures
    FROM DefGroup
 ),
 Record as 
 (
    SELECT d.CompanyId ,c.ComFeatures, d.DefFeatures, d.RoleId  From DefRecord d
        LEFT JOIN ComRecord c on c.CompanyId = d.CompanyId AND c.RoleId = d.RoleId
 ),
 FeatureList as 
 (
    select CompanyId, RoleId,  COALESCE(ComFeatures, DefFeatures) Features from Record
 ),
 Final as 
 (
    select CompanyId,RoleId, [ss].element from FeatureList 
    CROSS APPLY dbo.SplitToInt(Features, ',') as [ss]
    EXCEPT
    SELECT CompanyId,RoleId, FeatureId FROM DisabledCompanyFeature c WHERE c.CompanyId = CompanyId AND c.RoleId = RoleId
 )
SELECT *  FROM Final

This seems to be the answer I was looking for. I had to collapse the rows down and join on the single row using FOR XML PATH to do the comparison followed by expanding out the comma separated values once compared. The Coalesce in the select of the resulting query gets the item in order of precedence first from company set then ultimately default set if there is no match.