sql-servert-sqlcross-apply

Cross apply vs CTE in Sql Server


I have two tables
the first table is named: tblprovince

create table (
    provinceid int not null primary key (1,1) ,
    provinceNme nvarchar(max),
    description nvarchar(max))

the second table is named tblcity:

create table tblcity(
    cityid int identity (1,1), 
    CityName nvarchar(max), 
    population int, 
    provinceid int foreign key references tblprovince(provinceid)
);

I need to list all provinces that have at least two large cities. A large city is defined as having a population of at least one million residents. The query must return the following columns:

ā€‡

select p.provinceId, p.provincename, citysummary.LargeCityCount
from tblprovince p 
cross apply ( 
select count(*) as LargeCityCount from tblcity c 
 where c.population >= 1000000 and c.provinceid=p.provinceid
) citysummary 
where citysummary.LargeCityCount

Is this query correct?
Are there other methods that allow me to achieve my goal?


Solution

  • SELECT tp.provinceid, tp.provinceNme, COUNT(tc.cityid) AS largecitycount 
    FROM tblprovince tp INNER JOIN
    tblcity tc ON tc.provinceid=tp.provinceid
    WHERE tc.population>=1000000
    GROUP BY tp.provinceid, tp.provinceNme
    HAVING COUNT(tc.cityid)>1