How to count no of items in nested table

I have the following table

Region Table

ID

ParentID

RegionName

RestaurantTable

ID

RestaurantName

RegionID

What i tried to do is count the number of restaurants by specific regionname. My current query is

Select RegionID, RegionName, count(*) as RestaurantNo

From Region Inner Join Restaurant

On Region.ID = Restaurant.RegionID

However I only get the results below

RegionID RegionName RestaurantNO

1 A1 0

2 A1.1 2

3 A1.2 1

4 A1.3 0

Where A1.1 , A1.2, and A1.3 are children of A1 in Region table

The result is not correct due to A1 should have 3 in RestaurantNo due to it contains A1.1 , A1.2 and A1.3

Could anyone help me to solve this problem.

Thank you

You need to account for the hierarchy in your join. If you only have 2-deep regions then a simple OR base on ParentID will take care of the problem; however, if your nesting can go more than 2 deep you ought to use a CTE to account for all ancestor possibilities.

The simple alternative would append the OR condtion to your ON condition; something like:

Code Snippet

ON Region.id = Restaurant.RegionId
OR Region.ParentID = Restaurant.RegionId

If you need to take the CTE route, do a search of this forum for HIERARCHY to get some ideas.|||

I need to do on nested query and I tried to search for CTE but I couldn't get the job done, It's always return the incorrect results

WITH RegionRestaurantCount (RegionID, RegionName,ParentID, NumberOfRestaurants) AS
(
SELECT
RegionID,
RegionName,

ParentID,
(SELECT COUNT(*) FROM Region node
WHERE node.RegionID = parent.RegionID) as NumberOfRestaurants
FROM Region parent
)

SELECT rc.RegionName, rc.NumberOfRestaurants
FROM Restaurant res
INNER JOIN RegionRestaurantCount rc ON
res.RegionID = rc.RegionID
ORDER BY RegionName

Could you please help? Thank you|||

Try creating a function that returns a table including each row with its children, including itself. Then use that table to join to [restaurant] and do the aggregation.

Code Snippet

use tempdb

go

create table dbo.region (

ID int not null primary key,

ParentID int null references dbo.region(ID),

RegionName varchar(25) not null

)

go

create table dbo.restaurant (

ID int not null primary key,

RestaurantName varchar(25) not null,

RegionID int not null references dbo.region(ID)

)

go

insert into dbo.region values(1, null, 'A1')

insert into dbo.region values(2, 1, 'A1.1')

insert into dbo.region values(3, 1, 'A1.2')

insert into dbo.region values(4, 1, 'A1.3')

go

insert into dbo.restaurant values(1, 'r1', 2)

insert into dbo.restaurant values(2, 'r2', 2)

insert into dbo.restaurant values(3, 'r3', 3)

go

create function dbo.ufn_children (

@.ID int

)

returns table

as

return (

with cte

as

(

select ID, ParentID

from dbo.region

where ID = @.ID

union all

select

c.ID, c.ParentID

from

cte as p inner join dbo.region as c

on c.ParentID = p.ID

)

select *

from cte

)

go

;with bridge

as

(

select

a.ID as pID,

b.ID as cID

from

dbo.region as a

cross apply

dbo.ufn_children(a.ID) as b

)

select

c.pID,

count(*) as cnt

from

bridge as c

inner join

dbo.restaurant as d

on d.RegionID = c.cID

group by

c.pID

order by

c.pID

option (maxrecursion 0)

go

drop function dbo.ufn_children

go

drop table dbo.restaurant, dbo.region

go

AMB|||

Thank you for your solution however could you explain more details about the logic behind the function do, i am a little bit lost,also how can i translate it into stored procedure that I will use to retrieve the no_of_restaurants based on the regionID.

|||

1 - The function uses a recursive CTE to pull an [ID] and all its children, including itself. If you use that function to pull all childrens for every row in table [region], you will get something like this:

Example:

1 - 1

1 - 2

1 - 3

1 - 4

2 - 2

3 - 3

4 - 4

2 - If you join previous result to the table [restaurant], you will get:

1 - 2 - 1

1 - 2 - 2

1 - 3 - 3

2 - 2 - 1

2 - 2 - 2

3 - 3 - 3

so, if you group by first [ID] then you will get

1 - 3 rest

2 - 2 rest

3 - 1 rest

3 - Here is the sp

Code Snippet

create procedure dbo.usp_p1

@.ID int,

@.cnt int output

as

set nocount on

;with bridge

as

(

select

a.ID as pID,

b.ID as cID

from

dbo.region as a

cross apply

dbo.ufn_children(a.ID) as b

where

a.[ID] = @.ID

)

select

@.cnt = count(*)

from

bridge as c

inner join

dbo.restaurant as d

on d.RegionID = c.cID

group by c.pID

option (maxrecursion 0)

go

declare @.cnt int

exec dbo.usp_p1 1, @.cnt output

print @.cnt

go

AMB