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%'

robrodpProgrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
select * from table1 where exists (select null from table2 where table1.field1 like '%' + table2.field1 + '%')

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Qlemo"Batchelor", 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

sdstuberCommented:
doesn't adding distinct potentially change the results?
Qlemo"Batchelor", 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.
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.