CODEWITHSUNDEEP
sqlsql-server-2008comparesql-like
Brezhnews
Brezhnews

Reputation: 1569

How to use LIKE statement with multiple values from another field?

I want to select all records where field contains values from another field. How do I do that? Here is a code that I am trying.

select field1 , field2, field3 
from table1
where field1 like '%'+(select distinct field4 from table2)+'%'


Thanks in advance.

Upvotes: 1

Views: 1966

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Using the original structure of your query, you can do:

select field1, field2, field3 
from table1 t1
where exists (select 1
              from table2
              where t1.field1 like '%' + field4 + '%'
             );

The advantage of this method is that it will not duplicate records in table1. For instance, if there are two rows in table2 with the values 'a' and 'b' respectively and one row in table1 with the value 'ab', then this method will only return the row from table1 once.

Upvotes: 0

EvilPuppetMaster
EvilPuppetMaster

Reputation: 8350

Just do your like as a join condition:

select field1 , field2, field3 
from table1
join (select distinct field4 from table2) x
  on field1 like '%'+field4+'%'

Upvotes: 1

Related Questions