How to avoid conditional join in order to write a tsql view 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.
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.