Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Jan 2013
    Posts
    17
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Multi table, partial field match query

    Hi Folks

    I’m really hoping someone may be able to help with this one.

    I need to write a query that can find partially matching company names across a number of tables.

    For example, we have table1, table2 and table3 and all have a column named "companyname". These tables contain data spanning a good few years and in some cases a company may have changed it's name but in a subtle way. E.g.Lees Cakes Ltd becomes Lees Buns Ltd

    So, we are given a company name, "Lees Cakes Ltd" and we need to compare the "companyname" columns in our 3 tables to find and return any partially matching company names

    Can anyone point me in a general direction of what type of search I should use? Is there some kind of percentage match query in existence?

    Hope this makes sense, please point out if it doesn't and I'll try to explain myself more clearly.

    Should anyone be able to help, please send a photo and we will happily print it out and hang it on the wall of our office as hero of the month.

    Thanks in anticipation.

    *Edit - I've had an idea..........

    I think I'm looking at something like:

    Code:
    SELECT DISTINCT(companyname) FROM table1,table2,table3,table4,table5 WHERE companyname LIKE '%malvern%' OR LIKE '%windows%'
    and then using UNION to bring all the data together as my table columns and headers are the same for each table

    Am I in the right ballpark?
    Last edited by sitechooser; 03-15-2013 at 06:14 PM. Reason: I had an idea

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    UNION, yes, but nothing like what you showed.

    Code:
    SELECT companyname 
    FROM table1 
    WHERE companyname LIKE '%malvern%' OR companyname LIKE '%windows%'
    UNION
    SELECT companyname 
    FROM table2
    WHERE companyname LIKE '%malvern%' OR companyname LIKE '%windows%'
    UNION
    SELECT companyname 
    FROM table3 
    WHERE companyname LIKE '%malvern%' OR companyname LIKE '%windows%'
    (UNIONs are DISTINCT by default... you have to use UNION ALL to override the DISTINCT).

    Or you could do this:
    Code:
    SELECT DISTINCT X.companyname FROM
        ( SELECT companyname FROM table1
          UNION
          SELECT companyname FROM table2
          UNION
          SELECT companyname FROM table3
        ) AS X
    WHERE X.companyname LIKE '%malvern%' OR X.companyname LIKE '%windows%'
    Which is more efficient probably depends on the number of records in the various tables. You could/should try it both ways.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Notice that you can *NOT* do, as you suggested,
    Code:
    WHERE companyname LIKE '%malvern%' OR LIKE '%windows%'
    you *MUST* repeat the field name with each use of LIKE:
    Code:
    WHERE companyname LIKE '%malvern%' OR companyname LIKE '%windows%'
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    sitechooser (03-16-2013)

  • #4
    New Coder
    Join Date
    Jan 2013
    Posts
    17
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Thanks ever so much for that. Your assistance is very much appreciated


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •