0

I have two tables in SQL Server database:

category( 
   itemid, 
   parentid
)

ArticleAssignedCategories(
   categid, 
   artid
)

categid is a foreign key of itemid

I want to get count of artids and child of that for given itemid (child means categories with parentid of given itemid.)

For example; If given itemid = 1 and in table category have (3,1),(4,1)(5,3)

All of 3, 4, 5 are child of 1

Can anyone help me to write a good query?

shA.t
  • 16,580
  • 5
  • 54
  • 111
atabrizi
  • 908
  • 1
  • 13
  • 29
  • 1
    You can use a recursive CTE. http://stackoverflow.com/questions/12858567/how-to-get-all-children-and-itself-from-hierarchical-data-with-cte-in-sql-server – Tim Schmelter Aug 26 '13 at 11:35

2 Answers2

1

Recursive queries can be done using CTE

with CTE(itemid, parentid)
as (
  -- start with some category
  select itemid, parentid
  from category where itemid = <some_itemid>
union all 
  -- recursively add children
  select c.itemid, c.parentid
  from category c 
  join CTE on c.parentid = CTE.itemid
)
select count(*)
from ArticleAssignedCategories a
join CTE on CTE.itemid = a.categid
bwt
  • 17,292
  • 1
  • 42
  • 60
0

Here is the query. I hope this may help you

select b.artid,count(b.artid) from category a
inner join ArticleAssignedCategories b on a.itemid = b.artid
group by b.artid
Prabhakaran
  • 3,900
  • 15
  • 46
  • 113