CODEWITHSUNDEEP
sql-server-2005t-sqlrecursion
Jim Evans
Jim Evans

Reputation: 6515

Is this nasty recursion possible in T-SQL?

I have 3 tables:

Region
(
    Region_id int,
    Parent_id int,
    Region_name varchar(50)
)

RegionStore
(
    Region_id int,
    Store_id int
)

StoreItems
(
    Store_id int,
    Item_id int
)

The Region table is recursive in that regions can have parent regions and if a parent id is null that is a top level region. Regions go to a possible 4 levels.

I need to get an item count for every region at every level. If a region is a parent – I need the count for all its children down as many levels and branches as it goes. In other words if region 255 has parent 130 and 130 has parent 67 and 67 has parent 2 I need a count for 2 (including all children and branches) and a count for 67 including all its children and branches and so on. I need to get this for all regions in one call. Is this possible using recursive query?

Upvotes: 2

Views: 161

Answers (2)

Andrey Gurinov
Andrey Gurinov

Reputation: 2895

Try this:

DECLARE @Region TABLE 
( 
    Region_id int, 
    Parent_id int, 
    Region_name varchar(50) 
) 

DECLARE @RegionStore  TABLE
( 
    Region_id int, 
    Store_id int 
) 

DECLARE @StoreItems TABLE
( 
    Store_id int, 
    Item_id int 
) 

INSERT @Region
SELECT 2, NULL, '2' UNION ALL
SELECT 67, 2, '67' UNION ALL
SELECT 130, 67, '130' UNION ALL
SELECT 255, 130, '255' UNION ALL
SELECT 1, NULL, '1' UNION ALL
SELECT 68, 2, '68' 

-- add more test data here

;WITH CTE AS (
    SELECT  
        Region_id,
        Parent_id,
        Region_name,
        Region_id AS Region_id_calc
    FROM @Region

    UNION ALL   

    SELECT  
        r.Region_id,
        r.Parent_id,
        r.Region_name,
        CTE.Region_id_calc AS Region_id_calc
    FROM CTE
    INNER JOIN @Region AS r
        ON r.Region_id = CTE.Parent_id
)
SELECT  
    CTE.Region_id,
    Region_name,
    COUNT(DISTINCT Item_Id)
FROM CTE
INNER JOIN @RegionStore AS s
    ON CTE.Region_id_calc = s.Region_id
INNER JOIN @StoreItems AS i
    ON s.Store_id = i.Store_id
GROUP BY
    CTE.Region_id,
    Region_name
ORDER BY 
    CTE.Region_id

Upvotes: 3

Andomar
Andomar

Reputation: 238296

One way to do that is to include extra columns in the initial part of the recursive query. That allows you to "pass down" where the recursion started. In the example below, the extra columns remember the Root_id and Root_name. That should list all regions and their total item count:

; with  Regions as 
        (
        select  Region_id as Root_id
        ,       Region_name as Root_name
        ,       *
        from    @Region
        union all
        select  p.Root_id
        ,       p.Root_name
        ,       c.*
        from    Regions p
        join    @Region c
        on      p.Region_id = c.Parent_id
        )
select  r.Root_name
,       count(distinct si.Item_id) as ItemCount
from    Regions r
left join 
        @RegionStore rs 
on      rs.Region_id = r.Region_id
left join 
        @StoreItems si 
on      si.Store_id = rs.Store_id
group by
        r.Root_name

Working example on SE Data.

Upvotes: 1

Related Questions