Is there any way to use LIKE in JOINS?

  • GregLyon

    SSCommitted

    Points: 1946

    Hi, I'm trying to use some keywords from a field in one table as a filter to another table.  I've succeeded in writing some dynamic SQL that loops through each keyword and writes a WHERE clause similar to this: 

        WHERE
        mt.description LIKE '%lead%' OR
        mt.description LIKE '%chromium%' OR ...

    It works but there can be hundreds of keywords.  I'd rather be able to use some sort of join between my main table and the keywords table.  Something like this is what I have in mind:

        SELECT mt.description, kw.keyword 
        FROM MainTable mt

        INNER JOIN KeyWords kw

        ON mt.description LIKE '%' + kw.keyword + '%'
     
    This example does not work...Is there something like this that does work?  Am I stuck using my dynamic sql version?
    I'm on SQL Server 7.0.

  • Frank Kalis

    SSC Guru

    Points: 111183

    Simple answer: No

     

    Maybe something from this site will help you with your query:

    http://www.sommarskog.se

    Especially the articles on dynamic searches and dynamic sql might be helpful

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • GregLyon

    SSCommitted

    Points: 1946

    Thanks Frank,

    It's as I feared.  I'll have to proceed with my dynamic sql method.  Too bad, I was already thinking of lots of uses for it if this was possible...

    The link you gave looks like really good reading, thanks for it!

    Greg.

  • mccork

    SSC-Insane

    Points: 22288

    I don't see why such a query wouldn't work.  The following works fine for me on both SQL 2000 and 7.0.

     

    use pubs

    select t.title, a.state

    from titles t

    inner join authors a on t.title like '%' + a.state + '%'

     

    When you say it doesn't work, what error are you getting?  Are you sure your keyword is varchar?


    Cheers,
    - Mark

  • Frank Kalis

    SSC Guru

    Points: 111183

    Learn each day something new

    Your query works here fine, too, Mark.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • ekoner

    Mr or Mrs. 500

    Points: 551

    I'm testing Microsoft Reporting Services and using the database provided, this works:

    SELECT DISTINCT a.AddressId, a.AddressLine1, a.City

    FROM   Address a

    INNER JOIN Address b ON a.City LIKE '%Ken%' AND b.City LIKE '%ken%'

    Not sure what uses it would have, can you elaborate Greg ?

    Measure twice, cut once

  • David Burrows

    SSC Guru

    Points: 64592

    Same thing but using PATINDEX

    SELECT mt.description, kw.keyword

    FROM MainTable mt

    INNER JOIN KeyWords kw

    ON PATINDEX ('%' + kw.keyword + '%',mt.description) > 0

    Far away is close at hand in the images of elsewhere.
    Anon.

  • aochss

    SSCommitted

    Points: 1677

    We are using this very thing on SQL Server 2000 (Microsoft SQL Server  2000 - 8.00.760) to do a join on two "Hash" keys that we use to query based on a person's hierarchy in the company.

    For example a person coming in at a region level would have a hash of 1-100-12 (corporate-division-region) and we wanted to find all dealers in his region (as well as all of the people, who work at the dealers). 

    Examples: 1-100-12-123, 1-100-12-124, 1-100-12-134, etc.  The join looked something like this

    select * from hierarchy h

    inner join dealers d on h.haskey like (d.hashkey + '%')

    This works very good.  Hope this helps...

     

    Anton

  • vadba

    SSChampion

    Points: 11132

    If you only want one row per description in the result set, regardless of the number of keywords found, something like the following might work:

    SELECT mt.description

      FROM MainTable mt

     WHERE EXISTS (SELECT *

                     FROM KeyWords kw

                    WHERE mt.description LIKE '%' + kw.keyword + '%')

       ORDER mt.description

    Or, use a SELECT DISTINCT:

    SELECT DISTINCT mt.description

        FROM MainTable mt

        INNER JOIN KeyWords kw

        ON mt.description LIKE '%' + kw.keyword + '%'

       ORDER BY mt.description

    Otherwise, I agree with the others, your code worked as presented.

    Mike

     

  • williamhoos@yahoo.com

    Right there with Babe

    Points: 789

    Great posting and dialouge.  If you need to join a comma list to an Id (int) field, you just have to convert the ID field to varchar.  There should be a part 3 to the article on complex joins discussing the issues surrounding using like in a join.  Any performance issues?

    Example where AGLs is a comma list of IDs.  I add commas leading and trailing to avoid mismatching 1 and 10, etc.

    SELECT ER_ID,G_L_ID,G_L

    FROM

    A_I

    INNER JOIN G_L

    ON (',' + replace(A_I.AGLs,' ','') + ',')

      LIKE '%,' + CONVERT(nvarchar(20),G_L_ID) + ',%'

  • sdube

    SSC Enthusiast

    Points: 158

    Great example but bad performance compared to a dynamic sql.

    Using your example with LIKE does a table scan which is very costly.

    Using the IN directive do an INDEX SEEK if the field is an index (as in your example) and it is way faster.

    Looks like doing a dynamic SQL statement is the way to go.

     

  • GregLyon

    SSCommitted

    Points: 1946

    Hi everyone, first off, thanks for all the feedback!  I'm glad to know that it works, it seemed like it should have to me.

    1) Mark, Anton...Solved my problem.  I had reversed the LIKE in my actual query from how I posted it!  My result set always returned 0 records.  Go figure that I posted the SQL correctly here yet had it messed up in my real query and couldn't spot the difference yesterday...

    2) edafe...I'm using this to link chemical keywords to a description field in the main table.  In an ideal database all entries in the main table would use a key number to uniquely identify them...but this is far from that ideal database!

    3) sdude...While the performance will be worse using the LIKE join, the upside is that I can use other fields from my keyword table in the result set, worth it in my case.  On a desktop edition SQL installation with 600k rows in the main table, going aginst 50 keywords was about a 10 sec query to return 2500 records.  Certainly acceptable for my system.

    4) Everyone else...Thanks for your posts!  I'll be playing around with some of the other suggestions to see if I net any boost in performance.

    A sidenote...GRRR--my responses keep disappearing when I post them...Is there a time limit between starting to type and posting successfully?

     

  • GregLyon

    SSCommitted

    Points: 1946

    And the results...

    SQL 7.0 desktop db installation on a development machine.  Main table has 600k rows, keyword lookup using ~50 words.  Query run from ISQLW.

    PATINDEX and the LIKE JOIN take identical time to run, 12 sec, and return ~2500 rows.  Dynamic SQL saves about 40% timewise, but since there's no join to the keywords table I lose showing them or related fields in my result set. 

    As expected, indexing the description field of the main table had no effect on performance.

    So for my requirements I'm going to stick with the LIKE join.

    Thanks again!

  • Frank Kalis

    SSC Guru

    Points: 111183

    Hi Greg,

    let me say sorry for the bad advice I posted. Last week I made several remarkable poor postings here

    Hope quality will improve again when things calm down in privte life.

    Glad to see you found a solution anyway.

    ----------------------------------------

    Looks like doing a dynamic SQL statement is the way to go.

    Most members here know, I have a different opinion on the use of dynamic sql. Read the links I posted before, and decide then if that's the way to go.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply