I have a varchar field in my database table A let's call it store_name, this field gets its value from entity A, now entity B enters store_name into a different database table B now I want to get all records in table A where the store_name matches the values in table B.
How would you recommend me doing the query as I don't control the values of those 2 fields?
What do you think about PostgreSQL fuzzystrmatch? The tables contain thousands of records.
Thanks
Assuming that both table A and table B are in the same database. And I guess since you don't control insertion of data, you are not sure if the values are of same case or there may be a spelling mismatch.
Case 1: If the problem is only of case-mismatch, you can use ilike:
Select a.store_name
from a, b
Where a.store_name ilike b.store_name
Case 2: If you also want to check for spelling mismatch, but words sound similar, then after installing postgresql-contrib package and creating extension fuzzystrmatch, you can use:
Select a.store_name
from a, b
Where a.store_name ilike b.store_name OR
soundex(a.store_name) = soundex(b.store_name)
If you are dealing with names, which may not always be in English, it may be more appropriate to use metaphone or dmetaphone function instead of soundex.
Documentation: Fuzzystrmatch