Count # of children of parent in same table 1

[upplepop]

I have a self-referential table that contains parents and childen in the same table. Here is a sample of my table:

service_codes
------------------
[b]uid[/b]   [b]parent_id[/b]     [b]descrip[/b]
14     NULL         Basic Needs
15     14           Food
17     15           Emergency Food
59     14           Housing/Shelter
72     NULL         Education
265    NULL         Consumers
266    265          Consumer Assistance

I need query that counts the number of children each parent has. It doesn't have to count the grandchildren. So the data above would return the following:

[b]uid[/b]   [b]Descrip[/b]       [b]Children[/b]
14    Basic Needs      2
72    Education        0
265   Consumers        1

Thanks for your help.

 

[gmmastros]

You need to do a self join to accomplish this. I think your expected outcome is wrong because there is no parent_id for uid = 72. That being said, this will not be exactly what you want, but it should be a really good head start.

[green]-- Dummy up some data[/green]
Declare @service_codes Table(Uid Integer, Parent_Id Integer, Description VarChar(100))

Insert Into @Service_codes Values(14,NULL,'Basic Needs')
Insert Into @Service_codes Values(15,14,'Food')
Insert Into @Service_codes Values(17,15,'Emergency Food')
Insert Into @Service_codes Values(59,14,'Housing/Shelter')
Insert Into @Service_codes Values(72,NULL,'Education')
Insert Into @Service_codes Values(265,NULL,'Consumers')
Insert Into @Service_codes Values(266,265,'Consumer Assistance')

Select S.Uid, S.Description, Counts.ChildCount
From   @Service_codes S
       Inner Join (
         Select Parent.Uid, 
                Count(Child.Parent_id) As ChildCount
         From   @Service_Codes As Parent
                Left Join @Service_Codes As Child
                On [blue]Parent.Uid = Child.Parent_Id[/blue]
         Group by Parent.Uid
       ) As Counts On S.Uid = Counts.Uid

The part in blue is a little weird. You may want to use:
Parent.Parent_id = Child.Uid
instead.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[upplepop]

Worked great. thanks.