Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Conditional Join creating duplicates

Author  Topic 

blodzoom
Starting Member

28 Posts

Posted - 2014-10-24 : 18:24:05
I'm not sure what the best way to do this is... This is a piece of a much bigger query that already works. I'm trying to add a timezone component. I made a table called TimeZones:

TZ_Location|UTC_Offset

The location can be a city, state, country or region. If the city matches, I want that UTC offset, if the state matches, that one and so on getting less specific.

The issue that I'm having is that if the city and the state can both find a match, it creates duplicate records. I just want the first (most specific) match.


.
.
Coalesce(CityTZ.UTC_Offset, StateTZ.UTC_Offset, CountryTZ.UTC_Offset, RegionTZ.UTC_Offset) AS [Timezone]
.
.
.
LEFT JOIN TimeZones CityTZ
ON Sub2.BP_City = CityTZ.TZ_Location
LEFT JOIN TimeZones StateTZ
ON Sub2.BP_State = StateTZ.TZ_Location
LEFT JOIN TimeZones CountryTZ
ON cm.Country = CountryTZ.TZ_Location
LEFT JOIN TimeZones RegionTZ
ON cm.Region = RegionTZ.TZ_Location

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-24 : 18:27:34
Try this:

LEFT JOIN TimeZones tz
ON Sub2.BP_City = tz.TZ_Location
OR Sub2.BP_State = tz.TZ_Location
OR cm.Country = tz.TZ_Location
OR cm.Region = tz.TZ_Location


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

blodzoom
Starting Member

28 Posts

Posted - 2014-10-24 : 18:29:27
I will certainly try that. I've never seen it done like that (there's a lot I haven't seen). Does that give you the first join that matches? Does that mean I don't need the coalesce, just tz.tz_location in the select?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-24 : 18:31:15
Please test it. I don't have your data and tables to be able to test it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-10-24 : 18:31:30
I don't think you're guaranteed to get the most specific first match that way. Try this:


LEFT JOIN TimeZones CityTZ
ON Sub2.BP_City = CityTZ.TZ_Location
LEFT JOIN TimeZones StateTZ
ON CityTZ.UTC_Offset IS NULL AND
Sub2.BP_State = StateTZ.TZ_Location
LEFT JOIN TimeZones CountryTZ
ON CityTZ.UTC_Offset IS NULL AND
StateTZ.UTC_Offset IS NULL AND
cm.Country = CountryTZ.TZ_Location
LEFT JOIN TimeZones RegionTZ
ON CityTZ.UTC_Offset IS NULL AND
StateTZ.UTC_Offset IS NULL AND
CountryTZ.UTC_Offset IS NULL AND
cm.Region = RegionTZ.TZ_Location

Go to Top of Page

blodzoom
Starting Member

28 Posts

Posted - 2014-10-24 : 19:32:11
Thanks for your help guys, I will try to look deeper at this over the weekend.
Go to Top of Page

mole999
Starting Member

49 Posts

Posted - 2014-10-25 : 05:37:39
what do you do with your DST, especially when you go world wide, UK changes tonight but globally its not that easy

Mole
Go to Top of Page

blodzoom
Starting Member

28 Posts

Posted - 2014-10-30 : 12:58:31
I used ScottPelcher's plan and it works. Thanks, Scott.

Mole, in this case, luckily, I don't need to worry about DST. It doesn't need to be that exact and in some cases, we just pick a time zone in the middle of a country so that if a city or state doesn't match, it comes back with something that's at least close.
Go to Top of Page
   

- Advertisement -