SQL: Left Outer join that can switch what columns to join with depending on data inside the column

Question

I'm trying to join these two tables together to show the record in base and the null in history. The problem I am having is with a left outer join. when BO.val2 = 0: I want the join to use BO.VAL5 = TR.VAL5 only but when BO.val2 = TR.val2 I want it to use that for the join. The code below is a just idea. If you have any suggestion on how to do this that would be great!

 SELECT DISTINCT      
   BO.RUN_DATE,
   BO.val2,
   BO.val3,
   BO.val4,
   BO.VAL5
   TR.DTDATE,
   TR.val2,
   TR.val3,
   TR.val4,
   TR.val5
 FROM BASE BO                    
 LEFT OUTER JOIN HISTORY AS TR ON CASE 
   WHEN BO.val2 = 0 and  BO.VAL5 = TR.VAL5  THEN ????
   WHEN BO.val2 = TR.val2 and BO.VAL5 = TR.VAL5 then ???
   ELSE ??
   END 
 WHERE TRUNC(BO.POST_DATE)= TRUNC(SYSDATE)
 ORDER BY BO.VAL4  ;

Answer 1

Your both variants have BO.VAL5 = TR.VAL5 in common.

The second part of the logic is BO.val2 = TR.val2 or BO.val2 = 0.

So, final condition should be:

SELECT DISTINCT
    BO.RUN_DATE,
    BO.val2,
    BO.val3,
    BO.val4,
    BO.VAL5,
    TR.DTDATE,
    TR.val2,
    TR.val3,
    TR.val4,
    TR.val5
FROM
    BASE AS BO
    LEFT JOIN HISTORY AS TR
        ON BO.VAL5 = TR.VAL5 AND (BO.val2 = 0 OR BO.val2 = TR.val2)
WHERE
    TRUNC(BO.POST_DATE) = TRUNC(SYSDATE)
ORDER BY BO.VAL4;

Answer 2

What you'll need to do is join to History twice, using your different conditions. Then you can use a COALESCE() to return the data

  SELECT DISTINCT 
       BO.RUN_DATE,
       BO.val2,
       BO.val3,
       BO.val4,
       BO.VAL5
       COALESCE(TR.DTDATE, TR2.DTDATE) AS DTDATE,
       COALESCE(TR.val2, TR2.val2) AS val2,
       COALESCE(TR.val3, TR2.val3) AS val3,
       COALESCE(TR.val4, TR2.val4) AS val4,
       COALESCE(TR.val5, TR2.val5) AS val5
  FROM BASE BO                    
  LEFT OUTER JOIN History AS TR ON TR.VAL5 = BO.VAL5
  LEFT OUTER JOIN History AS TR2 ON TR2.VAL2 = BO.VAL2
  WHERE TRUNC(BO.POST_DATE)= TRUNC(SYSDATE)
  ORDER BY BO.VAL4 ;