T-sql – How to avoid conditional join in order to write a tsql view

join;t-sqlview

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.

Best Answer

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.

Related Solutions

Sql-server – How should row-specific metadata be created handled for an outer join view

If you're just trying to do a batched update to attach a bunch of tickets to an invoice (i.e. Invoices and tickets live in a 1:M relationship) then you have to get a list of ticket IDs into the stored procedure in the first place.

You could use a table variable as an input parameter if you have a version of SQL Server that supports these. Otherwise, you would have to encode the list of ticket IDs in some way, for example a list serialised as a string. You could encode the list as XML if you don't mind eternal damnation :)

If you're trying to add an Invoice record to a batch of tickets (i.e tickets live in a 1:0-1 relationship with invoices) then you have the same options for input.

In both cases the most efficient way to do the updates is likely to be creating a table variable with your list of invoice changes and then doing an update/insert operation joining the table variable against the main tables.

OTOH I can't think of anything more elegant than that.

If your problem is passing the list in across from the client via then you can pass in table-valued parameters in recent versions of ADO.NET. In older versions you are basically in the job of scrubbing and escaping the identifiers client side and passing the list across serialised as a string or XML (supported from .NET 2.0 and SQL Server 2005 onwards).

If you're using anything else (e.g. PHP) then you're probably going to have to drop down to the lowest common denominator - scrub the list of tickets and encode them as a string. Maybe the stored procedure can do some validation, such as checking the tickets all belong to the correct user.

Sql-server – Conditional Join – Specific Column Select

Regarding your comment about the scan, that happens because using ISNULL() in the join manipulates the field, so it's no longer SARGable. It may be faster to split this up, rather than try to do all the work in a single query. For example:

DECLARE @Recipient_ID INT, @Submitter_ID INT

SELECT 
@Recipient_ID = Order.Recipient_Id, 
@Submitter_ID = Order.Submitter_Id
FROM Order
-- where clauses

IF @Recipient_ID IS NULL

SELECT Emp.Full_Name
FROM Employee Emp
WHERE Emp.Emp_Id = @Submitter_ID

ELSE

SELECT Emp.Full_Name
FROM Employee Emp
WHERE Emp.Emp_Id = @Recipient_ID

EDIT based on comment: Since there can be many orders, what about this? It is SARGable so should index well, and you're not querying the table multiple times.

with cte as (
SELECT 
COALESCE(Order.Recipient_Id, Order.Submitter_Id) AS EmployeeID
FROM Order
-- where clauses
)

SELECT Emp.Full_Name
    FROM Employee Emp
INNER JOIN cte on cte.EmployeID = Emp.Emp_Id

Best Answer

Related Solutions

Sql-server – How should row-specific metadata be created handled for an outer join view

Sql-server – Conditional Join – Specific Column Select

Related Question