CODEWITHSUNDEEP
sqlsql-servert-sqlleft-join
ReneW
ReneW

Reputation: 61

Perform a left join based on one to multiple rows in both tables in SQL Server

One table contains codes representing parts of surgical operations. One procedure is described by one ore more codes (one row per code). Combinations of procedures may occure. One operation may e.g.consist of 1 procedure represented by one code and a second procedure represented by 3 codes (4 rows for this operation).

A left join has to be performed with a second table containing the codes for the procedures with one or many codes each in one row.

Of note, a certain code used to describe a procedure based on a total of three codes may also occure as a single code describing a different procedure like the last entry in table #Procedures. Hence, a simple join does not solve the problem.

In the present solution I use two nested while statement taking hours in SQL Server 2014. Is there a more efficiant way to perform this task, maybe using the xml functionality of SQL Server?

Any help appreciated.

Example:

CREATE TABLE #Procedures 
(
    ProcID INT, 
    NumberofCodes INT, 
    Codes NVARCHAR(20)
)

INSERT INTO #Procedures (ProcID, NumberofCodes, Codes) 
VALUES (1, 1, 'SingleCodeXYZ'), (2, 3, 'TripleCodeXY1'), (2, 3, 'TripleCodeXY2'),
       (2, 3, 'TripleCodeXY3'), (3, 2, 'DoubleCodeXY1'), (3, 2, 'DoubleCodeXY2'),
       (4, 1, 'SingleCodeABC'), (5, 1, 'TripleCodeXY2')

CREATE TABLE #KodesforOperations 
(
    OPID INT, 
    ProcID INT, 
    NumberofCodes INT, 
    Codes NVARCHAR(20)
)

INSERT INTO #KodesforOperations (OPID, NumberofCodes, Codes) 
VALUES (1, 4, 'SingleCodeXYZ'), (1, 4, 'TripleCodeXY1'), (1, 4, 'TripleCodeXY2'), 
       (1, 4, 'TripleCodeXY3'),
       (2, 1, 'SingleCodeABC'),
       (3, 2, 'DoubleCodeXY1'), (3, 2, 'DoubleCodeXY2')

--SELECT * FROM #Procedures
--SELECT * FROM #KodesforOperations

--nested While statements:
UPDATE A 
SET A.ProcID = B.ProcID 
FROM #KodesforOperations A 
LEFT JOIN #Procedures B On A.Codes = B.Codes
WHERE A.NumberofCodes >= 3 AND B.NumberofCodes >= 3

UPDATE A 
SET A.ProcID = B.ProcID 
FROM #KodesforOperations A 
LEFT JOIN #Procedures B On A.Codes = B.Codes
WHERE A.NumberofCodes >= 2 AND B.NumberofCodes >= 2

UPDATE A 
SET A.ProcID = B.ProcID 
FROM #KodesforOperations A 
LEFT JOIN #Procedures B On A.Codes = B.Codes
WHERE A.NumberofCodes >= 1 AND B.NumberofCodes >= 1

SELECT * FROM #KodesforOperations

Upvotes: 4

Views: 82

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269713

You should be able to combine the conditions. However, this is logically equivalent to just checking the third:

UPDATE A
    SET A.ProcID = B.ProcID 
    FROM #KodesforOperations A JOIN
         #Procedures B
         On A.Codes = B.Codes
WHERE A.NumberofCodes >= 1 AND B.NumberofCodes >= 1;

If this condition is true, then the comparisons to 2 and 3 are also true.

The LEFT JOIN is unnecessary, because you need a match in the two tables to test the WHERE condition.

Upvotes: 2

Related Questions