By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) Jul 25 2020 11:26:55 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
create table regions (Id int, ParentId int, Name varchar(20));
insert into regions values (1, null, 'EU');
insert into regions values (2, 1, 'Germany');
insert into regions values (3, 1, 'France');
select * from regions;
create table cities (Id int, Name varchar(20), RegionId int);
insert into cities values (1, 'Berlin', 2);
insert into cities values (2, 'Hamburg', 2);
insert into cities values (3, 'Paris', 3);
insert into cities values (4, 'Nice', 3);
select * from cities;
| Id | ParentId | Name |
|---|---|---|
| 1 | null | EU |
| 2 | 1 | Germany |
| 3 | 1 | France |
| Id | Name | RegionId |
|---|---|---|
| 1 | Berlin | 2 |
| 2 | Hamburg | 2 |
| 3 | Paris | 3 |
| 4 | Nice | 3 |
select R.Name, count(C.Id)
from Regions R
join Cities C on C.RegionId = R.Id
group by R.Name
having count(C.Id) > 1
| Name | (No column name) |
|---|---|
| France | 2 |
| Germany | 2 |
with flatregions as
(
select t.ID, t.ParentID, t.Name, 1 as lvl
from regions t
union all
select t.ID, tt.ParentID, tt.Name, t.lvl+1
from flatregions t
inner join regions tt on tt.ID = t.ParentID
)
select * FROM flatregions;
| ID | ParentID | Name | lvl |
|---|---|---|---|
| 1 | null | EU | 1 |
| 2 | 1 | Germany | 1 |
| 3 | 1 | France | 1 |
| 3 | null | EU | 2 |
| 2 | null | EU | 2 |
with flatregions as
(
select t.ID, t.ParentID, t.Name, 1 as lvl
from regions t
union all
select t.ID, tt.ParentID, tt.Name, t.lvl+1
from flatregions t
inner join regions tt on tt.ID = t.ParentID
)
select R.Name, count(C.Id) as CityCount
from flatregions R
join Cities C on C.RegionId = R.Id
group by R.Name
having count(C.Id) > 1
| Name | CityCount |
|---|---|
| EU | 4 |
| France | 2 |
| Germany | 2 |