SQL Join on Multiple condition-CodePudding

I have two tables tabA & tabB

joining these two tables using the below code:

 Select *
 from tabA X
 join tabB Y
 on (X.id1 = Y.id1
 Or X.id2 = Y.id2
 Or X.id3 = Y.id3)

Now, my requirement is like: I want if the 1st join condition is true, then the rest two join conditions on id2 & id3 will be discarded. Similarly if the 2nd join condition on id2 is true, then the rest of the join conditions on id1 & id3 will be discarded and so on.

Can you please tell how can I achieve this??

CodePudding user response：

You can score the matches and pick the one with the best score. For example:

select *
from (
  select x.*, y.*,
    row_number() over(partition by x.id1
      order by case when x.id1 = y.id1 then 3
                    when x.id2 = y.id2 then 2
                    else 1 end 
      desc
    ) as rn
  from tabA x
  join tabB y on x.id1 = y.id1
              or x.id2 = y.id2
              or x.id3 = y.id3
) x
where rn = 1

CodePudding user response：

     Select *
,JoinSource = case 
      when X.id1 = Y.id1 then 'id1' 
when X.id2 = Y.id2 then 'id2'
when X.id3 = Y.id3 then 'id3'
end 
 from tabA X
 cross join tabB Y
where
  case 
      when X.id1 = Y.id1 then 1 
when X.id2 = Y.id2 then 1
when X.id3 = Y.id3 then 1
end = 1

Remeber this code can lead a tablescan. You can consider using 3 queries with UNION (try first, if not match, try second).

CodePudding user response：

I would try UNION

SELECT * FROM TabA JOIN TabY ON(X.id1 = Y.id1)
UNION
SELECT * FROM TabA JOIN TabY ON(X.id2 = Y.id2)
UNION
SELECT * FROM TabA JOIN TabY ON(X.id3 = Y.id3)

Now, my requirement is like: I want if the 1st join condition is true, then the rest two join conditions on id2 & id3 will be discarded. Similarly if the 2nd join condition on id2 is true, then the rest of the join conditions on id1 & id3 will be discarded and so on.

I don't think this condition matters. How would the result set be different? If any of the conditions are true, the row will be included once because of the way SQL creates the result set. UNION will make SQL get the result set for the 3 queries then combine them and remove things with the same primary key.

The other reason I don't think it matters is depending on the optimizer in your SQL engine, it'll "short circuit" the OR chain as soon as one matches, which would fulfill your requirement. This is dependent on both the SQL flavor and the exact query and optimizer.

You may also be able to use your ON(... OR ... OR ...) with a SELECT DISTINCT

CodePudding user response：

Join tabA with LEFT joins to 3 copies of tabB each joined only if the previous copies failed and in the WHERE clause filter out any rows that did not match at all:

SELECT *
FROM tabA a
LEFT JOIN tabB b1 ON b1.id1 = a.id1
LEFT JOIN tabB b2 ON b2.id2 = a.id2 AND b1.id1 IS NULL
LEFT JOIN tabB b3 ON b3.id3 = a.id3 AND b1.id1 IS NULL AND b2.id2 IS NULL
WHERE COALESCE(b1.id1, b2.id2, b3.id3) IS NOT NULL;

CodePudding user response：

With first_match as (select *
                       from tabA X
                       join tabB Y
                         on (X.id1 = Y.id1)),
    second_match as (select * from 
                       from tabA X
                       join tabB Y
                         on (X.id2 = Y.id2)
                      where not exists (select 1 
                                          from first_match f  
                                          where f.id1=y.id1)), 
      third_match as (select * from 
                        from tabA X
                        join tabB Y
                          on (X.id3 = Y.id3)
                       where not exists (select 1  
                                           from first_match f  
                                          where f.id1=y.id1)
                         AND not exists (select 1 from second_match s  where s.id2=y.id2))
select * from first_match
union all
select * from second_match
union all
select * from third_match;

First match will return all rows that matches the condition. Second will return matches but will skip the records that has been already retrieved by first. Similarly, third will return rows that has a match and has been skipped by first and second.