nested query with group by and inner join

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » nested query with group by and inner join

nested query with group by and inner join

Rate Topic

Display Mode

Topic Options

Author

Message

28.kanikasoni

Posted Monday, December 03, 2012 11:06 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 18, 2013 2:57 AM
Points: 15, Visits: 62

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

Post #1392252

« Prev Topic | Next Topic »

Permissions