Suppose that I have two tables Region
and Devices
. Region
provides tree structure (by self referencing a ParentRegionId
foreign key). Every device belongs to one region (by the RegionId
foreign key).
See this fiddle for a better understanding of the tables.
I need to count 'Region''s devices including nested ones similar to this:
+---------+------------+--------------+
| RegioId | RegionName | DevicesCount |
+---------+------------+--------------+
| 0 | Region0 | 5 |
| 1 | Region1 | 2 |
| 2 | Region2 | 2 |
| 11 | Region11 | 1 |
| 12 | Region12 | 0 |
| 21 | Region21 | 0 |
| 22 | Region22 | 1 |
+---------+------------+--------------+