Join tables without primary key, foreign key that will also return the null values when joining the-CodePudding

I have these tables: table1:

Code1  Code2  Code3  Code4  ISCode5
 xx     NULL  TEST1  TEST1    1
 yy     zzz   TEST2  TEST2    1
NULL     ss   TEST3  TEST3    1
NULL    aaa   TEST4  TEST4    0

table2:

ID    ColTest1   ColTest2   Code1  Code2
 1      2            3        xx     NULL  
 1      3            4        yy     zzz
 2      5            6       NULL    ss
 2      5            6       NULL    aaa

Expected Output:

 ID    ColTest1   ColTest2   Code1  Code2
 1      2            3        xx     NULL  
 1      3            4        yy     zzz
 2      5            6        NULL    ss

I need to join table1 and table 2 where IsCode5 = 1. But there is no primary key and foreign key for both tables. The only common columns for both tables is Code1 and Code2.

I have tried this:

SELECT T2.ID ,
          T2.ColTest1,
          T2.ColTest2,
          T2.Code1 ,
          T2.Code2 
  FROM 
          [dbo].[table2] T2 
  INNER JOIN [dbo].[table1] T1 
           ON T1.Code1 = T2.Code1
           AND T1.Code1 = T2.Code2
   WHERE T1.ISCode5 = 1

But it only returns:

ID    ColTest1   ColTest2   Code1  Code2  
 1      3            4        yy     zzz

Do you have any idea to return the expected result? Thanks.

CodePudding user response：

I think you just need OR instead of AND

-- DDL
declare @Table1 table (Code1 varchar(2), Code2 varchar(3), Code3 varchar(5), Code4 varchar(5), ISCode5 bit);
declare @Table2 table (ID int, ColTest1 int, ColTest2 int, Code1 varchar(2), Code2 varchar(3));

-- DML
insert into @Table1 (Code1, Code2, Code3, Code4, ISCode5)
values
('xx', NULL, 'TEST1', 'TEST1', 1),
('yy', 'zzz', 'TEST2', 'TEST2', 1),
(NULL, 'ss', 'TEST3', 'TEST3', 1),
(NULL, 'aaa', 'TEST4', 'TEST4', 0);

insert into @Table2 (ID, ColTest1, ColTest2, Code1, Code2)
values
(1, 2, 3, 'xx', NULL),  
(1, 3, 4, 'yy', 'zzz'),
(2, 5, 6, NULL, 'ss'),
(2, 5, 6, NULL, 'aaa');

-- Query
select T2.ID, T2.ColTest1, T2.ColTest2, T1.Code1, T1.Code2
from @Table1 T1
inner join @Table2 T2 on t1.code1 = t2.code1 or t1.code2 = t2.code2 -- <= OR not AND
where ISCode5 = 1;

Results:

ID	ColTest1	ColTest2	Code1	Code2
1	2	3	xx	NULL
1	3	4	yy	zzz
2	5	6	NULL	ss

Note: if you add the DDL DML as shown here you make it much easier for people to answer.