I have two tables, each has a customer name field, which
is what I want to link the tables with. Using an example,
on one table ("master"), I have a listing of 25000
customer entries with names, address, customer #'s, etc.
On the other table, I have a list of 1000 customer names
whose information I want to match and pull from the other
table. However, the customer name on the master table is
Fleet Boston LLC, where as on the smaller table the
customer name is Fleet. I want to be able to link these
two to pull all of the information (rather than typing
each individual name in the criteria), but I can't
determine if this can be done, as the joining properties
pull information if it is an exact match.
This is one reason that most developers assiduously avoid EVER using
names as linking fields. A Primary Key has three desired attributes:
it's unique, stable, and short. Company names (and even more so,
people's names) fail on all three counts!
Is Fleet Enemas a match to Fleet? How about the hypothetical G. R.
Fleet LLC? What - in terms that a fast-but-excessively-literal
computer can understand - constitutes a "partial match"?
The best you can do, to my knowledge, is to use a LIKE clause in the
join:
SELECT <whatever>
FROM small INNER JOIN large
ON large.CompanyName LIKE small.CompanyName & "*";
This will match "Fleet" with "Fleet Boston LLC", and also with "Fleet
Enemas", but not with "G. R. Fleet LLC".
You could also put a * before the companyname, but that would get many
more false hits and might not work at all, since a leading wildcard
blocks use of indexes.
