joined if value is present

Question

hello,
I am joining table1 and table2
each table has several columns

I would like to have inner join if there is value in both table1.column3 and table2.column3
then the query becomes something like:

select
table1.column1, table2.column1...
from
table1.column3 inner join table2.column3

if there is no value in any of these columns table1.column3 and table2.column3
then use join on other fields such as

select
table1.column1, table2.column1...
from
table1.column1 inner join table2.column1 and table1.column2 inner join table2.column2 and
table1.column5 inner join table2.column5

how is this done?
thank you

Answer 1

Hi @arkiboys

As others have said, it is not clear and may not be what you are looking for.

If col3 are both null.
This query is for the case where col1, col2, and col5 are join conditions

SQL

declare @tbl1 table(col1 int,col2 int,col3 int,col4 int,col5 int)  
declare @tbl2 table(col1 int,col2 int,col3 int,col4 int,col5 int)  
  
insert into @tbl1 values  
(1,2,3,4,5),(1,2,null,4,5)  
insert into @tbl2 values  
(1,2,3,4,5),(1,2,null,4,5);  
  
select *   
from   
@tbl1 as tbl1 inner join @tbl2 as tbl2  
on   
(tbl1.col3 is not null and tbl2.col3 is not null  
and tbl1.col3 = tbl1.col3)  
or   
(tbl1.col3 is null and tbl2.col3 is null  
and tbl1.col1 = tbl2.col1  
and tbl1.col2 = tbl2.col2  
and tbl1.col5 = tbl2.col5  
);  

OUTPUT

Answer 2

As Yitzhak says, your question is not very clear. But as I understand, you could do something like:

   SELECT ...  
   FROM  table1 t1  
   CROSS APPLY (SELECT TOP 1 WITH TIES t.2.*  
                FROM  (SELECT 1 AS prioorder, t.2*  
                       FROM   table2 t2  
                       WHERE  t2.col3 = t1.col3  
                       UNION ALL   
                       SELECT 2 AS prioorder, t.2*  
                       FROM   table2 t2  
                       WHERE  t2.col4 = t1.col4  
                       UNION ALL  
                       SELECT 3 AS prioorder, t.2*  
                       FROM   table2 t2  
                       WHERE  t2.col5 = t1.col5) AS u  
                ORDER BY prioorder) AS t2