comparing two tables to look for mismatches and a way to edit the table value

[srathi]

Hello Everyone,

I am trying to compare data in the country field between two tables to look for differences. The problem is one table has value as "United States" and the other has it as "USA". Another example is England and United Kingdom. So it takes both of these as a mismatch.

I cannot really change value in either of the tables and so I need a way to edit value just for my query and get results.

thanks for me helping me out.....
S :)

[code green]

I have had similar dilemma with our product range.
Their name can vary between countries as well as having name variations and nicknames.
We found a way around by adding an alias field which grew to an alias table.
In other words a simple look up table with name variations is one possible solution

[srathi]

Thanks! How do I do that?

I have had similar dilemma with our product range.
Their name can vary between countries as well as having name variations and nicknames.
We found a way around by adding an alias field which grew to an alias table.
In other words a simple look up table with name variations is one possible solution

[ck9663]

I could be wrong, that sounds like what I would call an intermediate table. You have a table with 2 columns: CountryAlias and CountryName. You relate your table to this table using the CountryAlias and return the CountryName. The initial population of this table will be the unique list of what you currently have on your transaction table. The bloody part is to look at this list one record at a time and try to figure out if you need to insert it on your intermediate table or not.

There could be two way to implement this:

1. You can insert all country on this table with some of the values CountryAlias = CountryName

The cons: It's not really relational.
The pros: If there's CountryName that is not properly "named" and it's not on this table, you'll be able to identify it.

2. Insert those who needs alias, if the Country that you're looking for is not on this table that means it's properly "named".

The pros: It's somehow relational.
The cons: If there's a CountryName that's not on this list and you assumed that it's the proper name (even if it's not), you won't find it, at least not immediately.

Good luck.

-- CK

[code green]

That is correct ck9663.
The donkey work is collecting the data to populate the table,
although with countries it won't be that much.
And yes, 2 columns is all that is needed

Country    Alias
==========================
USA        United States of America
USA        The States
UK          United Kingdom
UK          Great Britain

I would add another pro.
A simple JOIN is all that is required to make comparisons with the main table