CODEWITHSUNDEEP
sqlsql-server-2008
User26
User26

Reputation: 13

SQL -Single quote values pass to where IN

I have two table, Exapmle table A - with data 

A
ID  value
1   [email protected],[email protected]

B
ID  Email
1   [email protected]
2   [email protected]
3   [email protected]

I need records from table B where its match with Table A

Select * from B where Email IN (select * from A where ID=1)

I don’t want to use dynamic query. I have tried this - 

Select * from B where Email IN (select REPLACE(stuff((select ',' + ''''+ 
cast(value as varchar(max)) + '''' from A where ID=1  
for xml path('')), 1, 1, ''),',',''','''))

But its not showing any result.

Please help me.

Upvotes: 0

Views: 42

Answers (2)

Alireza Shabani
Alireza Shabani

Reputation: 64

Try this code: 

Select *
      from B 
       inner join A on ',' + A.email + ',' like '%,' + B.email + ',%';

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You should fix your data structure. One simplistic method uses like:

select b.*
from b
where exists (select 1
              from a
              where ',' + a.email + ',' like '%,' + b.email + ',%';

Performance will be awful, but that is the price you pay for a really bad data structure.

Upvotes: 2

Related Questions