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  ;

score 1 · Answer 1 · answered Feb 16 '17 at 05:28

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;

answered Feb 16 '17 at 05:28

Vladimir Baranov

31,799
5
53
90

@BeauJenkins, Welcome to Stack Overflow. Please note that the preferred way of saying 'thanks' around here is by up-voting good questions and helpful answers (once you have enough reputation to do so), and by accepting the most helpful answer to any question you ask (which also gives you a small boost to your reputation). Please see the [About] page and also [How do I ask questions here?](http://stackoverflow.com/help/how-to-ask) – Vladimir Baranov Feb 16 '17 at 22:53

score 0 · Answer 2 · answered Feb 15 '17 at 23:29

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 ;

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

2 Answers2