CODEWITHSUNDEEP
sqlsql-server
Pat Doyle
Pat Doyle

Reputation: 384

Conditional Inner Join Statements in SQL Server

I am trying to join two tables in SQL Server. Their are no PK / FK in the tables so I have to join on a few conditions.

What I want to have happen is if I am unable to match on my first criteria I then want to join on different criteria. I was thinking there could be way to do this using a case statement... If it matches on my first condition then all is good, if it does not match on the first condition then try to join on a different condition.

Here is what I have so far. 

SELECT *
FROM Table1
INNER JOIN Table2
      ON Table1.DOS = CAST(SUBSTRING(Table2.SurgeryDate, 6, 2) + '/' + SUBSTRING(Table2.SurgeryDate, 9, 2) + '/' + SUBSTRING(Table2.SurgeryDate, 1, 4) AS DATE)
         AND Table1.DateOfBirth = CAST(SUBSTRING(Table2.BirthDate, 6, 2) + '/' + SUBSTRING(Table2.BirthDate, 9, 2) + '/' + SUBSTRING(Table2.BirthDate, 1, 4) AS DATE)
         AND Table1.Gender = CASE WHEN Table2.gender = 'Male' THEN 'M' 
                                  WHEN Table2.gender = 'Female' THEN 'F' 
                                  ELSE 'U'  
                             END
         AND LTRIM(RTRIM(Table1.HspId)) = LTRIM(RTRIM(Table2.HspId))
---If those people who do not match on the above criteria then I would want to match them on their last name.

Upvotes: 1

Views: 891

Answers (1)

Aaron Dietz
Aaron Dietz

Reputation: 10277

Sample data will be needed to give a specific answer, but here are a couple general approaches:

You can use OR while ruling out the previous condition:

SELECT cols
FROM Table1 t1
JOIN Table2 t2 ON t1.col1 = t2.col1
               OR (t1.col1 <> t2.col1 AND t1.col2 = t2.col2)

Note you have to rule out the previous condition to create the join predicate hierarchy and avoid duplicates.

Another way is to use repeated LEFT JOINs for each condition and add NULL handling:

SELECT COALESCE(t2.col1, t3.col1)
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.col1 = t2.col1
LEFT JOIN Table2 t3 ON t1.col2 = t3.col2
WHERE COALESCE(t2.col1, t3.col1) IS NOT NULL

COALESCE() returns the first non NULL value. The LEFT JOINs will return NULL if the join does not find a match row. Thus, the select COALESCE() is handling 'displaying' the correct column based on the join hierarchy order. The where COALESCE() is filtering out the rows where none of joins are successful (recreating the INNER JOIN you have in your current query)

The second approach, while ugly, can perform better because OR queries have trouble optimizing properly.

Upvotes: 3

Related Questions