Any way to join tables using preferential logic on ID?

I've got two tables, both with multiple IDs, none of which 开发者_Python百科are primary.

I'd like to join table2 to table1 first on ID1 if a match exists in table2. If no match exists for a given record, match on ID2; finally, if no match exists on ID2, match on ID3.

I'd started to set this up using a CASE statement similar to the following:

select * 
from table1 t
inner join table2 s
on CASE 
    WHEN s.ID1 = t.ID1 THEN s.ID1 = t.ID1
    WHEN s.ID2 = t.ID2 THEN s.ID2 = t.ID2
    ELSE s.ID3 = t.ID3
    END

but SQL Server doesn't seem to like it.

Any ideas about how to set something like this up?

declare @table1 table (ID1 int, ID2 int, ID3 int)
declare @table2 table (ID1 int, ID2 int, ID3 int)

insert into @table1 values (1, 2, 3)
insert into @table2 values (1, 1, 1)
insert into @table2 values (1, 2, 2)
insert into @table2 values (2, 2, 2)
insert into @table2 values (3, 3, 3)

select * 
from @table1 t
  cross apply (select top 1 with ties * 
               from @table2 t2
               where t2.ID1 = t.ID1 or
                     t2.ID2 = t.ID2 or
                     t2.ID3 = t.ID3 
               order by case 
                          when t2.ID1 = t.ID1 then 1
                          when t2.ID2 = t.ID2 then 2
                          when t2.ID3 = t.ID3 then 3
                        end) as S

Result:

ID1         ID2         ID3         ID1         ID2         ID3
----------- ----------- ----------- ----------- ----------- -----------
1           2           3           1           1           1
1           2           3           1           2           2

Try splitting into separate cases each side of the =, like this:

select * 
from table1 t
inner join table2 s
on 
   CASE 
    WHEN s.ID1 = t.ID1 then s.ID1
    WHEN s.ID2 = t.ID2 THEN s.ID2
    ELSE s.ID3
   END = 
   CASE 
    WHEN s.ID1 = t.ID1 then t.ID1
    WHEN s.ID2 = t.ID2 THEN t.ID2
    ELSE t.ID3
   END 

Although this may not perform well, it should work.

The join requires a boolean result. You just need to have a comparison operator. Try

inner join table t on
CASE     
   WHEN s.ID1 = t.ID1 THEN 1    
   WHEN s.ID2 = t.ID2 THEN 1       
   WHEN s.ID3 = t.ID3 THEN 1
   ELSE 0  
END = 1

You may get better performance using 3 joins and some UNIONs, something similar to:

select * 
from table1 t
inner join table2 s
ON s.ID1 = t.ID1

UNION

select * 
from table1 t
inner join table2 s
ON s.ID1 != t.ID1 AND s.ID2 = t.ID2

UNION

select * 
from table1 t
inner join table2 s
ON s.ID1 != t.ID1 AND s.ID2 != t.ID2 AND s.ID3 == t.ID3