Multi table, partial field match query

[sitechooser]

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:

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?

[Old Pedant]

UNION, yes, but nothing like what you showed.

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:

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.

[Old Pedant]

Notice that you can *NOT* do, as you suggested,

WHERE companyname LIKE '%malvern%' OR LIKE '%windows%'

you *MUST* repeat the field name with each use of LIKE:

WHERE companyname LIKE '%malvern%' OR companyname LIKE '%windows%'

[sitechooser]

Thanks ever so much for that. Your assistance is very much appreciated