ms sql query. Value of a field in one table included in a field in another table

I do this:
select * from table1 where field1 like '%john%'  or field1 like '%mike%'

For 2 or 3,4  values it is ok.

I need table2 with one field and n records

John
Mike
Fred
....
name_n
And  query that will work as the select I am using, except taking the field from table2

select * from table1 where field1 like '%john%'  or field1 like '%mike%'.... field1 like '%name_n%'

robrodpAsked:
Who is Participating?
 
sdstuberCommented:
select * from table1 where exists (select null from table2 where table1.field1 like '%' + table2.field1 + '%')
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Something like this?
select distinct t1.* from table1 t1, table t2 where field1 like '%'+t2.field1+'%';

Open in new window

0
 
sdstuberCommented:
doesn't adding distinct potentially change the results?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Yes, it sorts the result. The exists approach is probably performing better, anyway, as it will not require sort, only maintaining of an internal bitmap.
0
 
sdstuberCommented:
I meant actually eliminating data, not just the sorting.  but now that I think about it more, the only time that would happen would if table1 had duplicate rows. so it's likely fine for most real data.

I do agree though that the exists should be more efficient though
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.