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
: 
