joining based on columns priority

Question

I want to join 2 tables based on columns priority

ex. Suppose Table1 has six columns(Col1,Col2,Col3,Col4,Col5,Col6) If i want to join Table 1 with table2 (Col1,Col2,Col3,Col4,Col5,Col7), it should

otherwise

Select Table2.col7
where 
first check col1 , col2 and col3 if match found no need go check more 
second check col1 , col2  if match found no need go check more 
third check col1  if match found no need go check more 
last ignore all col1 , col2 and col3 
AND Table1.Col4=Table2.Col4
AND Table1.Col5=Table2.Col5

I may not be clear with my words, if any concern please shout

Use 'OR' instead of 'AND'. If match found the other conditions are not checked — Dany, Jun 22 '16 at 11:03
(!!!) UPD: I'm wrong: http://stackoverflow.com/questions/8900631/does-oracle-use-short-circuit-evaluation — Dany, Jun 22 '16 at 11:10

vercelli · Answer 1 · 2016-06-22T14:34:57.383

0

Select t2.col7
  from Table1 t1 inner join Table2 t2
    on
  case 
    when t1.col1 = t2.col1 then 1
    when t1.col2 = t2.col2 then 1
    when t1.col3 = t2.col3 then 1
    when t1.Col4=t2.Col4
       and t1.Col5=t2.Col5 then 1
    else 0 end = 1
  ;

edited Jun 22 '16 at 14:34

answered Jun 22 '16 at 11:15

vercelli

4,717
2
13
15

@user6498752 I edited the answer. tell if that's what you are looking for – vercelli Jun 22 '16 at 12:35
Thanks Vercelli for your time, but i have one more issue here, selection should base on t1.col4 and t1.col5 values , what i mean is if i have t1.col4 values as (A,B) and t1.col5 (1,0), so we'll have 4 options for combination (t1.col4, t1.col5) =(A,1),(A,0),(B,0),(B,1) for each combination, it goes for below checks case when t1.col1 = t2.col1 then 1 when t1.col2 = t2.col2 then 1 when t1.col3 = t2.col3 then 1 else 0 end = 1 Hope, you'll help me soon for this also ...many thanks in advance – user6498752 Jun 22 '16 at 12:36
@vercelli: `CASE` expressions don't belong in the `WHERE` clause. Use simple `AND` and `OR` instead. – Thorsten Kettner Jun 22 '16 at 12:51
@vercelli: And the join syntax you are using was made redundand ages ago. Use explicit joins instead (`INNER JOIN`, `CROSS JOIN` etc.). – Thorsten Kettner Jun 22 '16 at 12:53
@ThorstenKettner - Changed to inner join. I don't like CASE on WHERE but I'm not sure if they belong or not there. I just found it useful in this strange case. – vercelli Jun 22 '16 at 14:36
You use `CASE` when you need to create an evaluation of a boolean expression; in `WHERE` and `ON` you don't need to create any; they already are such evaluations. Your `ON` clause translates to: `ON t1.col1 = t2.col1 OR t1.col2 = t2.col2 OR t1.col3 = t2.col3 OR (t1.Col4 = t2.Col4 AND t1.Col5 = t2.Col5)`. – Thorsten Kettner Jun 22 '16 at 16:54

score 0 · Answer 2 · answered Jun 22 '16 at 12:48

0

You cannot tell SQL to try to join on a certain condition first and in case it finds no match to go on searching. What you can do is join all allowed combinations (matches on col4 and col5 in your case) and then rank your matches (such that a match on col1 and col2 and col3 is considered best etc.). Then only keep the best matches:

select col7
from
(
  select 
    t1.*, 
    t2.*,
    row_number() over 
    (
      partition by t1.col4, t1.col5
      order by case 
        when t2.col1 = t1.col1 and t2.col2 = t1.col2 and t2.col3 = t1.col3 then 1
        when t2.col1 = t1.col1 and t2.col2 = t1.col2 then 2
        when t2.col1 = t1.col1 then 3
        else 4
    ) as rn
  from table1 t1
  join table2 t2 on t2.col4 = t1.col4 and t2.col5 = t1.col5
)
where rn = 1;

answered Jun 22 '16 at 12:48

Thorsten Kettner

89,309
7
49
73

Thanks thanks Thorsten and Vercelli, I am trying to run below code, but throwing error BTW, I have introduced one more table now. – user6498752 Jun 22 '16 at 13:32
select DISTINCT CU.ID cu_id, 'XXXX', to_char(RR.EFF_FROM,'DD-MON-YYYY') Active_Date, NVL(to_char(RR.EFF_TO,'DD-MON-YYYY'), '31-DEC-9999') Inactive_Date from ( Select RL.*, CU.*, RR.*, row_number() over ( partition by RR.Divi_Flag, RR.Brand_Type_Code order by case when RR.Dept_Cd = FLOOR(RL.Sub_Section/10000) and RR.Section = FLOOR(MOD(RL.Sub_Section,10000)/100) and RR.SubSection = MOD(RL.Sub_Section,100) then 1 when RR.Dept_Cd = FLOOR(RL.Sub_Section/10000) and RR.Section = FLOOR(MOD(RL.Sub_Section,10000)/100) then 2 – user6498752 Jun 22 '16 at 13:32
when RR.Dept_Cd = FLOOR(RL.Sub_Section/10000) then 3 else 4 ) as rn from TABLE1 RL , TABLE2 CU , TABLE3 RR WHERE RL.Code = CU.FK_Rl_Code AND RL.Brand_Type_Code = RR.Brand_Type_Code and NVL(CU.Dividend_Flag, 'X') = NVL(RR.Divi_Flag, 'X') WHERE cu.skeletal_full_ind = 'F' AND cu.discontinued_date IS NULL ) where rn = 1; – user6498752 Jun 22 '16 at 13:32
@user6498752 - That seems to me like a new question. Is the original question answered? – vercelli Jun 22 '16 at 14:37
No, its an original one, but going by thorsten's suggestion. if you have any other approch pls let me know. – user6498752 Jun 22 '16 at 14:56
On a quick glance: `WHERE cu.skeletal_full_ind` should be `AND cu.skeletal_full_ind`. (Apart from that you should't use pre-1992 joins anymore. Use `FROM table1 rl JOIN table2 cu ON ...` instead. Or are you using an Oracle version older than 9i?) – Thorsten Kettner Jun 22 '16 at 17:03

joining based on columns priority

2 Answers2