Recursive joins

user_163573 0
Viewed 290

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

Regions

Id ParentId Name
1 null EU
2 1 Germany
3 1 France

Cities

Id Name RegionId
1 Berlin 2
2 Hamburg 2
3 Paris 3
4 Nice 3

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

Region CityCount
EU 4
Germany 2
France 2

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.

recursive-query sql-server sql
2 Answers

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

user_20299830

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;
ID ParentID Name lvl
1 EU 1
2 1 Germany 1
3 1 France 1
3 EU 2
2 EU 2

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
Region CityCount
EU 4
Germany 2
France 2

See this db<>fiddle.

user_140157370
Related