SQL Server 2019

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