Joining Tables on Similar, but not identical, Fields?

[Phoenix22]

Hi,

Is there a way I could join two tables on fields that are similar (i.e. using the "like" operator instead of = ?) I'm trying to join two tables, but due to inconsistent data input, it's not joining many records because there's extra words or extra letters added to the end of the field.

i.e. in Table A, the field called city would have a value of "Toronto", but in Table B, the field called city would be "Toronto, Ontario". These two records should be joined, but are not.

Thanks in advance for your help.

 

[GHolden]

Hi, you can use LIKE, example below....

SELECT * FROM A JOIN B ON A.FIELD LIKE B.FIELD + '%'

This will join on A.FIELD matches the beginning of B.FIELD

Obviously will only work if all the additional text in the field is in B.FIELD

e.g.

A.FIELD B.FIELD
JOHN JOHN SMITH
FRED FRED BLOGGS

would not work for...

A.FIELD B.FIELD
JOHN SMITH JOHN
FRED FRED BLOGGS

you would only get FRED to FRED BLOGGS joined. If your data is like this you'll have to do multiple queries.

Hope this helps.



There are two ways to write error-free programs; only the third one works.

 

[GHolden]

Correction.

You do not need multiple queries for...

A.FIELD B.FIELD
JOHN SMITH JOHN
FRED FRED BLOGGS

Just change to...

SELECT * FROM A JOIN B ON (A.FIELD LIKE B.FIELD + '%') OR (B.FIELD LIKE A.FIELD + '%')

There are two ways to write error-free programs; only the third one works.

 

[SQLSister]

Personally, I would try to clean up the data and put in validations to keep it cleaned up. Queries with a join on two like clauses are likely to be very inefficient, especially as your data grows.

 

[GHolden]

SQLSister:

Good point I did mean to add a similar caveat to my last post.



There are two ways to write error-free programs; only the third one works.