...

View Full Version : Multi table, partial field match query



sitechooser
03-15-2013, 02:26 PM
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
03-15-2013, 08:13 PM
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
03-15-2013, 08:15 PM
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
03-16-2013, 08:36 PM
Thanks ever so much for that. Your assistance is very much appreciated



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum