SQL LEFT JOIN with conditional CASE statements

sql sql-server tsql join case

28,612

Solution 1

It doesn't matter which of the conditions causes the rows to match in a join. There are legitimate reasons to use a case expression in a join but I think you just want to or your conditions and then use the case expression to output a ranked reason for the match.

SELECT *, CASE WHEN <condition1> THEN 1 WHEN <condition2> THEN 2 END as match_code
FROM T LEFT OUTER JOIN J ON <condition1> or <condition2>

I don't know what to picture regarding the "nested INDEX/MATCH" from Excel. If I'm on the wrong track above then perhaps you're looking for a nested case expression?

Now if your conditions will have matches across different rows and you only want to keep one then...

WITH matches AS (
    SELECT *, CASE WHEN <condition1> THEN 1 WHEN <condition2> THEN 2 END AS match_code
    FROM T LEFT OUTER JOIN J ON <condition1> OR <condition2>
), ranked as (
    SELECT *, MIN(match_code) OVER (PARTITION BY ???) AS keeper
    FROM matches
)
SELECT ...
FROM ranked
WHERE match_code = keeper

Solution 2

Well, you can always have several conditions in your CASE Statements:

  SELECT *
FROM T

left JOIN J ON 
  CASE
    WHEN condition1 THEN 1 --prefer this option even if CASE2 has a value
    WHEN condition2 And !condition1 THEN 2
  ELSE 0
END = 1

--UPDATED-- If both of your conditions are required to match, but condition1 is optional then you can try this statement too:

  SELECT *
FROM T  
left JOIN J ON 
  CASE
    WHEN condition1 And condition2 THEN 1 --Both conditions match
    WHEN condition2 THEN 2 -- condition1 has no match
  ELSE 0
END = 1

28,612

Author by

Orin Moyer

Updated on October 14, 2020

Comments

Orin Moyer over 3 years
Hopefully this is a quickie
```
SELECT *
FROM T
left JOIN J ON 
  CASE
    WHEN condition1 THEN 1 --prefer this option even if CASE2 has a value
    WHEN condition2 THEN 2
    ELSE 0
  END = 1 (edit: but if 1 does not satisfy, then join on 2)
```
Both cases return results, but I want THEN 1 to supersede THEN 2 and be the lookup priority

Can I have SQL do something like join on max(CASE)?

Basically I am trying to duplicate a nested INDEX/MATCH from Excel

edit: what i am hearing is that the Case should stop at the first returned TRUE, but it doesn't behave like that when i test
```
SELECT *
FROM T
left JOIN J ON 
  CASE
    WHEN condition1 THEN 1 --prefer this option even if CASE2 has a value
    WHEN condition2 THEN 1
    ELSE 0
  END = 1
```
it seems to prefer the 2nd THEN 1 sometimes, but not always... is there anything i am missing that would cause this behavior?
GSazheniuk over 8 years

But as @Amir answered in his comment, if condition1 matches then SQL Server won't match any other conditions and would stop by choosing first option.
Orin Moyer over 8 years

I think what I am trying to do is tell SQL that I want to try a JOIN on both conditions but return result for 1, and if there is no result for 1, then goto 2
GSazheniuk over 8 years

Well, actually that's how it works! :) Have a look: msdn.microsoft.com/en-us/library/ms181765.aspx - The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied.
Orin Moyer over 8 years

so if.. I set both conditions THEN 1.. it is supposed to stop at the first satisfied condition, and not go any further into the CASE.. correct?
Orin Moyer over 8 years

This sort of works in a work-around kind of way. not quite what i am looking for
Rom Eh over 8 years

With this way, you may improve your performances, but I'm agree, it's a workaround.
Orin Moyer over 8 years

. It looks like i can sort this by result set, but i cannot tell it which condition to prefer
GSazheniuk over 8 years

Look, you have CASE with two WHEN options, no matter how many conditions has any of them. For whichever WHEN all of the conditions specified are match, that WHEN is processed and SQL Server will stop going any further. If there is no WHEN condition for which all of the conditions are match, then server processes with ELSE statement. If there is no ELSE statement then final value will be NULL.
Rom Eh over 8 years

You can also make the CASE WHEN in the SELECT statement. Let me know the conditions, to make the query more relevant
Orin Moyer over 8 years

Here is the actual code if that helps, I don't know how to format in comment area left JOIN [CC_Note_Log].[dbo].[Desk_Mapping] D ON CASE WHEN S.tag1=D.Location and S.tag5=D.Line THEN 1 WHEN S.Location2ID=D.TwoID and S.tag5=D.Line THEN 1 WHEN S.tag1='LKW' and S.tag5=D.Line THEN 1 ELSE 0 END=1
Rom Eh over 8 years

Could it help ? SELECT * FROM T OUTER APPLY (SELECT TOP 1 CASE WHEN S.tag1=D.Location THEN 1 WHEN S.Location2ID=D.TwoID THEN 2 WHEN S.tag1='LKW' THEN 3 END, S.* FROM S WHERE S.tag5=D.Line) S If no case suits, you will get a null value.
Rom Eh over 8 years

Let us continue this discussion in chat.
Orin Moyer over 8 years

I think this is the best approach. Select CASE and CTE