question

kasimmohamed-6993 avatar image
0 Votes"
kasimmohamed-6993 asked EchoLiu-msft commented

use in and like operator in where condition

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

sql-server-generalsql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sorry for not creating example table

Then it's difficult for us to assist you.



0 Votes 0 ·

lol... i have added sample script.

thanks

0 Votes 0 ·

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @kasimmohamed-6993

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:
110842-image.png


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.



image.png (3.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered
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 + '%')

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.