By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT *
INTO dbo.Regions
FROM (VALUES(1,NULL,'EU'),
(2,1,'Germany'),
(3,1,'France'))V(ID,ParentID,[Name])
SELECT *
INTO dbo.Cities
FROM (VALUES(1,'Berlin',2),
(2,'Hamburg',2),
(3,'Paris',3),
(4,'Nice',3))V(ID,[Name], RegionID)
7 rows affected
WITH rCTE AS(
SELECT R.ID,
R.ParentID,
R.[Name]
FROM dbo.Regions R
UNION ALL
SELECT R.ID,
R.ParentID,
C.[Name]
FROM dbo.Regions R
JOIN rCTE C ON R.ParentID = C.ID)
SELECT *
FROM rCTE
| ID | ParentID | Name |
|---|---|---|
| 1 | null | EU |
| 2 | 1 | Germany |
| 3 | 1 | France |
| 2 | 1 | EU |
| 3 | 1 | EU |
WITH rCTE AS(
SELECT R.ID,
R.ParentID,
R.[Name]
FROM dbo.Regions R
UNION ALL
SELECT R.ID,
R.ParentID,
C.[Name]
FROM dbo.Regions R
JOIN rCTE C ON R.ParentID = C.ID)
SELECT r.[Name],
COUNT(*) AS CityCount
FROM rCTE r
JOIN dbo.Cities C ON r.ID = C.RegionID
GROUP BY r.[Name];
| Name | CityCount |
|---|---|
| EU | 4 |
| France | 2 |
| Germany | 2 |
DROP TABLE dbo.Cities;
DROP TABLE dbo.Regions;