469,590 Members | 2,464 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,590 developers. It's quick & easy.

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

12
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 :)
Mar 6 '08 #1
4 1878
code green
1,726 Expert 1GB
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
Mar 6 '08 #2
srathi
12
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
Mar 6 '08 #3
ck9663
2,878 Expert 2GB
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
Mar 6 '08 #4
code green
1,726 Expert 1GB
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
Expand|Select|Wrap|Line Numbers
  1. Country    Alias
  2. ==========================
  3. USA        United States of America
  4. USA        The States
  5. UK          United Kingdom
  6. UK          Great Britain
I would add another pro.
A simple JOIN is all that is required to make comparisons with the main table
Mar 6 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Ian N | last post: by
3 posts views Thread by Robert Dell | last post: by
41 posts views Thread by Odd-R. | last post: by
3 posts views Thread by Random Person | last post: by
11 posts views Thread by dskillingstad | last post: by
2 posts views Thread by Scamjunk | last post: by
5 posts views Thread by Franck | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.