Joining properties using partial lookup

  • Thread starter Thread starter Leesha H
  • Start date Start date
L

Leesha H

Can you join properties of tables using a partial match?
I am trying to link two tables. I want to have
information returned to me if the two fields joining the
tables have a partial match (not just limiting to an exact
match). Is this possible, and if so, how?

Thanks
 
Can you join properties of tables using a partial match?
I am trying to link two tables. I want to have
information returned to me if the two fields joining the
tables have a partial match (not just limiting to an exact
match). Is this possible, and if so, how?

Thanks
Click to expand...

Sometimes, depending on just what you mean by "a partial match". Could
you cite an example with sample data?
 
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.
 
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.
Click to expand...

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.
 
Back
Top