I have two tables defined below (note that the Regions table is recursive and that the recursion can potentially have many levels).

Regions

Cities

I want to see how many cities there are in a particular region. Desired output below:

This query gives me the count of cities in every child region, but how do I join in the recursive table to also get the parent (in this case EU) region?

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

I've tried to simplify a real-world problem I'm facing, this is obviously the simplification.

CodePudding user response：

This appears to be what you're after. You can use an rCTE to move through the hierachy to the root, but each iteration retains certain information; in this case the name of the original node. Then you can still JOIN on the RegionID:

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];

db<>fiddle

CodePudding user response：

You can use a recursive CTE to flatten your regions tree:

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;

Then use that CTE in the JOIN of your cities table:

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

See this db<>fiddle.