CODEWITHSUNDEEP
sqlsql-servert-sqljoincase
Orin Moyer
Orin Moyer

Reputation: 509

SQL LEFT JOIN with conditional CASE statements

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?

Upvotes: 6

Views: 47265

Answers (4)

shawnt00
shawnt00

Reputation: 17915

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

Upvotes: 3

Rom Eh
Rom Eh

Reputation: 2063

You can use the CROSS APPLY /OUTER APPLY operator : https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

SELECT *
FROM T
    OUTER APPLY (SELECT TOP 1 *
                 FROM J
                 WHERE condition1 OR condition2
                 ORDER BY order) J

Upvotes: 0

Balinti
Balinti

Reputation: 1534

You can use With statement to this in 2 steps: 

 With first_join as  
    (SELECT *
    FROM T
    left JOIN J ON condition1)
      select * from first_join 
    join J On case when Name_of_2nd_condition is null 
       then     condition2 
      ELSE null end

Upvotes: 0

GSazheniuk
GSazheniuk

Reputation: 1384

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

Upvotes: 1

Related Questions