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?
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