use in and like operator in where condition

Question

Hi,

I have two table. Table1 has three columns and table2 has one column
i need to select data from table1
where table1.column2 whatever exists in table2.column1 (here i have to use 'in')
or (use or condition)
where table1.column3 exists like in table2.column1 (here i have to use 'like')

Hi
create table #table1 (id float, PCode nvarchar(50), TCode nvarchar(200));
create table #table2 (Code nvarchar(200));
create table #result (id float, PCode nvarchar(50), TCode nvarchar(200));

insert into #table1 values (1001, 'P1001', 'T1001')
insert into #table1 values (1001, 'P1002', 'T1001,XYZ')
insert into #table1 values (1001, 'P1003', 'T1001')
insert into #table1 values (1001, 'P1004', 'BBB')
select * from #table1;

insert into #table2 values ('ABC');
insert into #table2 values ('P1001');
insert into #table2 values ('P1003');
insert into #table2 values ('XYZ');
select * from #table2;

insert into #result values (1001, 'P1001', 'T1001')
insert into #result values (1001, 'P1003', 'T1001')
insert into #result values (1001, 'P1002', 'T1001,XYZ')
select * from #result;

drop table #table1;
drop table #table2;
drop table #result;

i need like result table. the table1 PCode (P1001,P1003) exists in table2. like wise TCode (XYZ) exists in table2.

i tried like below
select id,PCode,TCode from #table1
where PCode in (select code from #table2) or TCode like (select '%'+ Code +'%' from #table2) ;

Thanks

Thanks

Answer 1

Hi @kasim mohamed ，

Please also check:

SELECT t1.*  
FROM #table1 t1  
WHERE PCode IN (SELECT Code FROM #table2)  
OR EXISTS (SELECT *FROM #table2 t2  
           WHERE t1.TCode LIKE '%'+t2.Code+'%')  

OR:

SELECT DISTINCT id,PCode,TCode   
FROM #table1 t1  
JOIN #table2 t2  
ON PCode in (SELECT code FROM #table2)  
OR  TCode like '%'+ t2.Code  +'%'   

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

SELECT t1.id, t1.PCode, t1.TCode
FROM   #table1 t1
WHERE  EXISTS (SELECT *
               FROM   #table2 t2
               WHERE  t1.PCode = t2.Code OR
                      t1.TCode LIKE '%' + t2.Code + '%')