title: conditional joining in oracle
body: <p>Conditional joining statement : - </p>
  
  <p><strong>High level Description  :-</strong> 
  Join table based on two columns if combination is not present then join on one table - </p>
  
  <p><strong>Detailed Table -</strong> </p>
  
  <pre><code>create table tab1
  (tab1_col1 number not null,
  tab1_col2 number null,
  tab1_col3 varchar(10));
  </code></pre>
  
  <p><strong>Lookup Table</strong></p>
  
  <pre><code>create table lkp1
  (lkp_col1 number not null,
  lkp_col2 number not null,
  lkp_col3 number not null,
  lkp_col4 varchar(10));
  </code></pre>
  
  <p><strong>Insert Statement - 
  tab1</strong></p>
  
  <pre><code>insert into tab1 values (10,101,'A');
  insert into tab1 values (12,101,'B');
  insert into tab1 values (11,102,'C');
  insert into tab1 values (13,103,'B');
  insert into tab1 values (14,104,'C');
  insert into tab1 values (15,108,'A');
  insert into tab1 values (16,102,'D');
  </code></pre>
  
  <p><strong>Lookup Table
  lkp1</strong></p>
  
  <pre><code>insert into lkp1 values (10,101,50,'PICK');
  insert into lkp1 values (10,101,50,'PICK');
  insert into lkp1 values (11,102,100,'SKIP');
  insert into lkp1 values (11,110,50,'PICK');
  insert into lkp1 values (13,103,40,'PICK');
  insert into lkp1 values (13,103,60,'PICK');
  insert into lkp1 values (14,199,100,'PICK');
  insert into lkp1 values (15,115,80,'PICK');
  insert into lkp1 values (15,115,20,'PICK');
  </code></pre>
  
  <p><strong>Requirement was</strong> - 
  Join table based on tab1_col1=lkp_col1 
  and 
  tab1_col2=lkp_col2
  Filter out lookup table data lkp_col4=SKIP
  If record not present in lookup table for then give default value(99.99).
  (All records from tab1 table should be selected).</p>
  
  <p>I built this query and it was working fine. </p>
  
  <pre><code>SELECT tab1_col1, tab1_col2, NVL (lkp_col3, '99.99') "LKP_COL3"
    FROM tab1,
         (SELECT *
            FROM lkp1
           WHERE lkp_col4 = 'PICK') lkp
   WHERE tab1_col1 = lkp_col1(+) AND tab1_col2 = lkp_col2(+)
  </code></pre>
  
  <p><strong>Now requirement changed</strong> 
  First check if 
  tab1_col1=lkp_col1 
  and 
  tab1_col2=lkp_col2
  If lookup table is not having data for this combination 
  then check again with 
  tab1_col1=lkp_col1 
  If this is not also available then give dafault value.</p>
  
  <p>Database - Oracle 10g</p>
  
  <p><strong>What I have tried so far</strong></p>
  
  <p>After lot of trail and error I m able to get the output. However, is there a better or simple way to use this ?</p>
  
  <pre><code>SELECT tab1_col1, tab1_col2, LKP_COL3
    FROM tab1,
         (SELECT *
            FROM lkp1
           WHERE lkp_col4 = 'PICK') lkp
   WHERE tab1_col1 = lkp_col1 AND tab1_col2 = lkp_col2
   union all
   SELECT tab1_col1, tab1_col2, LKP_COL3
    FROM tab1,
         (SELECT *
            FROM lkp1
           WHERE lkp_col4 = 'PICK') lkp
   WHERE tab1_col1 = lkp_col1(+)
   AND  
   LKP_COL1|| '-' || LKP_COL2 not in( SELECT tab1_col1|| '-' || tab1_col2
    FROM tab1, lkp1 lkp
   WHERE tab1_col1 = lkp_col1 AND tab1_col2 = lkp_col2)
   order by 1
  </code></pre>