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

Posted on 2011-10-06
Last Modified: 2012-06-21
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

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

Question by:robrodp
    LVL 73

    Accepted Solution

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

    Assisted Solution

    Something like this?
    select distinct t1.* from table1 t1, table t2 where field1 like '%'+t2.field1+'%';

    Open in new window

    LVL 73

    Expert Comment

    doesn't adding distinct potentially change the results?
    LVL 67

    Expert Comment

    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.
    LVL 73

    Expert Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now