StackOverflow_53575163, Sql Server

StackOverflow_53575163

create table all_categories 
(
  record_id int,
  parent_category_id int,
  parent_id int,
  title varchar(30),
  level int
);

insert into all_categories 
(record_id, parent_category_id, parent_id, title, level) values
 (24, NULL, NULL, 'Real Estate', 0)
,(5915, 24, 24, 'Residential', 1)
,(7569, 5915, 24, 'For sale', 2)
,(25, NULL, NULL, 'Unreal Estate', 0)
,(8847, 25, 25, 'For unsale', 1)
;

select * from all_categories

create table listings (
  record_id int primary key, 
  cat_id int
);

insert into listings 
(record_id, cat_id) values
 (1, 7569)
,(2, 8847);

create function f_total_listings
(
    @root_id int
) 
with execute as caller
returns int 
as
begin
    DECLARE @listing_count int;
    
    with cat (record_id, parent_category_id) AS
    (
        select root.record_id, root.parent_category_id
        from all_categories AS root
        where root.parent_category_id = @root_id
        union all
        select child.record_id, child.parent_category_id
        from cat as parent, all_categories as child
        where parent.record_id = child.parent_category_id
    )
    select @listing_count = count(*)
    from listings l
    join cat on l.cat_id = cat.record_id;
    return @listing_count;
end;
 
select record_id, f_total_listings(record_id) from all_categories


run \| edit \| history \| help	0