Hi Guys
I am trying to do something reasonably complex and need a bit of help. I have a table (tbl_DW_Twitter _Location_Parse ) with these columns
ID, items, twitter_screen_ name, location_orig, time_zone_orig, location_result , Parsed_Date
The twitter_screen_ name is foreign key to a table called tbl_Twitter_Pro file, so in tbl_DW_Twitter_ Location_Parse it can appear multiple times. The latter table basically stores the location results of a location parsing function for each twitter_screen_ name and could have up to around seven rows per screen name that can be either (field = location result) a country, region or city. You can get more than one country, city or region per screen name..
Here is an example below
ID Location twitter_screen_ name Location_Result
1 Atlanta catbugle City
3 GA catbugle Region
5 US catbugle Country
6 Georgetown catbugle City
What I need to do is
1. Validate each entry against the other entries for the same twitter_profile name by comparing combinatins against a master reference table that contains combo of country,region and city in a single row.. for example taking the City Atlanta here, and seeing whether it is assocaited with the region and the country for the twitter_screen_ name in question (i.e. Atlanta and Region GA and Country = US - does this combo exist on the master ref table, if not does just Atlanta and Region or Atlanta and Country exist?)
2. I then need to merge the multiple rows into a single row that would contain columns with the validated locations
twitter_screen_ name, city, region, country
3. If we have a city and region that is valid but not for the country, then we need to pull back alternative country from the master ref table that contains that combination of city / region and drop the invalid country...
Thanks
I am trying to do something reasonably complex and need a bit of help. I have a table (tbl_DW_Twitter _Location_Parse ) with these columns
ID, items, twitter_screen_ name, location_orig, time_zone_orig, location_result , Parsed_Date
The twitter_screen_ name is foreign key to a table called tbl_Twitter_Pro file, so in tbl_DW_Twitter_ Location_Parse it can appear multiple times. The latter table basically stores the location results of a location parsing function for each twitter_screen_ name and could have up to around seven rows per screen name that can be either (field = location result) a country, region or city. You can get more than one country, city or region per screen name..
Here is an example below
ID Location twitter_screen_ name Location_Result
1 Atlanta catbugle City
3 GA catbugle Region
5 US catbugle Country
6 Georgetown catbugle City
What I need to do is
1. Validate each entry against the other entries for the same twitter_profile name by comparing combinatins against a master reference table that contains combo of country,region and city in a single row.. for example taking the City Atlanta here, and seeing whether it is assocaited with the region and the country for the twitter_screen_ name in question (i.e. Atlanta and Region GA and Country = US - does this combo exist on the master ref table, if not does just Atlanta and Region or Atlanta and Country exist?)
2. I then need to merge the multiple rows into a single row that would contain columns with the validated locations
twitter_screen_ name, city, region, country
3. If we have a city and region that is valid but not for the country, then we need to pull back alternative country from the master ref table that contains that combination of city / region and drop the invalid country...
Thanks