nested query with group by and inner join

• 28.kanikasoni

  Mr or Mrs. 500

  Points: 526

  More actions

  December 3, 2012 at 11:06 pm

  #285717

  select *

  from geodata.TB_City A inner join

  (select citydefaultname, CityCountryID, avg(CityLatitude) 'CityLatitude', avg(CityLongitude) 'CityLongitude' from geodata.TB_City

  group by citydefaultname, CityCountryID) B on

  A.CityCountryID = b.CityCountryID and a.CityDefaultName = b.CityDefaultName and ISNULL(ISNULL(a.CityProvinceID_3,a.CityProvinceID_2),a.CityProvinceID_1) = ISNULL(ISNULL(b.CityProvinceID_3,b.CityProvinceID_2),b.CityProvinceID_1)

  I want to use condition on CityProvinceID_3,CityProvinceID_2,CityProvinceID_1 but group by only on citydefaultname,CityCountryID