CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Rod
Rod

Reputation: 15457

count or bool of child records

Would like to return count of child records for each record in results?

lists
listid,name
----------------
1,foods
2,fruits
3,veggies
4,counties
5,blah /* other list types */


listitems
listid,listitemId,listname
----------------
1,1,fruits
2,1,veggies
3,3,carrots
4,3,broccoli

Here's the query I have to work with:

declar @listid int
select @listid = 1 --as an example
select * from listitems where listid=@listid

results:
1,1,fruits
2,1,veggies

Goal: I'd like to display the count of items that exist for fruits and veggies in this example

listitems
listitemid,listid,name,childcount
1,1,fruits,0
2,1,veggies,2

Here's how I came up with the answer:

select *,
(
    select (select case when COUNT(*)>0 then 1 else 0 end as reccount 
    from ListItems li3 where li3.listid = l.listid) 
    from Lists l where li2.name = l.listname
) as haschildrecords
 from ListItems li2 where listid=1

I am still deciding if I want to return a count or bit...

Is this correct or the best way?

Upvotes: 0

Views: 181

Answers (2)

Rod
Rod

Reputation: 15457

select *,
(
    select (select case when COUNT(*)>0 then 1 else 0 end as reccount 
    from ListItems li3 where li3.listid = l.listid) 
    from Lists l where li2.name = l.listname
) as haschildrecords
 from ListItems li2 where listid=1

Upvotes: 0

podiluska
podiluska

Reputation: 51494

Is this what you're after?

select
  lists.listid,
  lists.name, 
  count(distinct listitemid) as itemcount, 
  convert(bit,sign(count(distinct listitemid) ) ) as itemexists    
from
  lists 
     left join
  listitems on lists.listid = listitems. listid
group by 
  lists.listid,lists.name

I can't tell, but if you're constructing a hierarchical structure, then you'll want to look at common table expressions, and/or HierarchyID

Upvotes: 1

Related Questions