Conditional Join creating duplicates

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/

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?

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/

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

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.

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

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources