How to Use Case Statement in a Join Condition

Can I use CASE statement in a JOIN condition?

A CASE expression returns a value from the THEN portion of the clause. You could use it thusly:

SELECT  * 
FROM    sys.indexes i 
    JOIN sys.partitions p 
        ON i.index_id = p.index_id  
    JOIN sys.allocation_units a 
        ON CASE 
           WHEN a.type IN (1, 3) AND a.container_id = p.hobt_id THEN 1
           WHEN a.type IN (2) AND a.container_id = p.partition_id THEN 1
           ELSE 0
           END = 1

Note that you need to do something with the returned value, e.g. compare it to 1. Your statement attempted to return the value of an assignment or test for equality, neither of which make sense in the context of a CASE/THEN clause. (If BOOLEAN was a datatype then the test for equality would make sense.)

Use Case Statement in Join

IT should be,

ON 
ts.ACCOUNTID =  CASE
                    WHEN ts.ACCOUNTTYPE = '1' THEN SI.TENANCYID
                    WHEN ts.ACCOUNTTYPE = '2' THEN SI.EMPLOYEEID
                    WHEN ts.ACCOUNTTYPE = '3' THEN SI.SUPPLIERID
                    WHEN ts.ACCOUNTTYPE = '4' THEN SI.SALESCUSTOMERID
                END

SQL Join with case statement

You need to use LEFT JOIN and handle with COALESCE clause to handle NULL values coming from tables. E.g, If both order_install, order_remove can have OrderDetailname and one of them will give values, based on the typeId.

You need to have COALESCE(order_install.OrderDetailName, order_remove.OrderDetailName) AS OrderDetailName

SELECT  o.*, -- COALESCE( You need to have COALESCE clause here to handle values coming from both tables
FROM  orders o
LEFT OUTER join order_stage_install on order.stageid=order_stage_install.id
AND o.typeId = 1
LEFT OUTER JOIN order_stage_remove  on order.stageid=order_stage_remove.id
AND o.typeId = 2

SQL LEFT JOIN with conditional CASE statements

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

Joining two tables based on Case statement

Is this possible using Case statement ...?

Below is simplified / dummy example for BigQuery Standard SQL

#standardSQL
WITH `project.dataset.tableA` AS (
  SELECT 1 colsA, 'apple' name union all
  SELECT 2, 'lemon' union all
  SELECT 3, 'peach'
), `project.dataset.tableB` AS (
  SELECT 4 colsB, 'apple' name union all
  SELECT 5, 'orange' union all
  SELECT 6, 'melon'
)
SELECT 
  a.name AS a_name,
  b.name AS b_name,
  colsA,
  colsB
FROM `project.dataset.tableA` a
JOIN `project.dataset.tableB` b
ON CASE 
    WHEN a.name = 'apple' AND b.name IN ('apple', 'orange') THEN TRUE
    WHEN a.name = 'peach' AND b.name IN ('peach', 'melon') THEN TRUE
  END  

with the output :

Row a_name  b_name  colsA   colsB    
1   apple   apple   1       4    
2   apple   orange  1       5    
3   peach   melon   3       6      

... or any other way?

Even simplified version of above is

#standardSQL
SELECT 
  a.name AS a_name,
  b.name AS b_name,
  colsA,
  colsB
FROM `project.dataset.tableA` a
JOIN `project.dataset.tableB` b
ON (a.name = 'apple' AND b.name IN ('apple', 'orange'))
OR (a.name = 'peach' AND b.name IN ('peach', 'melon'))    

obviously with same output

---

Related Topics