December 7, 2006 at 2:48 pm
I have two tables that have a primary/foreign key relationship. What would my join syntax look like if I wanted to count the number of child records for each parent record so that my output looks like this:
ParentID Title NumChildren
I know that it is probably pretty simple, but I'm having a hard time with it.
Thanks in advance,
Jim
December 7, 2006 at 3:00 pm
If a parent has no children, do you want the parent reported with zero for NumChildren, or excluded from the resultset completely ?
December 7, 2006 at 3:07 pm
0 or null
December 7, 2006 at 5:55 pm
Try this:
select P.ParentID, Title, NumChildren
from ParentTable P left join
(select ParentID, NumChildren = count(*) from ChildTable group by ParentID) as C
on P.batch_id = C.batch_id
The above gives NULL when there are no children. To get 0, change to:
select P.ParentID, Title, isnull(NumChildren, 0)
from ParentTable P left join
(select ParentID, NumChildren = count(*) from ChildTable group by ParentID) as C
on P.batch_id = C.batch_id
Hope this helps.
December 8, 2006 at 7:26 am
ooops! data for the previous post
create table t1(f1 int, f2 int)
create table t2(f1 int, f2 int)
insert into t1 values(111,222)
insert into t1 values(222,222)
insert into t1 values(333,222)
insert into t1 values(444,222)
insert into t1 values(555,222)
insert into t2 values(222,333)
insert into t2 values(222,333)
insert into t2 values(222,333)
insert into t2 values(333,333)
insert into t2 values(333,333)
insert into t2 values(111,333)
December 8, 2006 at 7:26 am
select t1.f1, count(t2.f1)
from t1 left outer join t2 on t1.f1 = t2.f1
group by t1.f1
December 8, 2006 at 5:52 pm
Yah, I noticed yesterday that I had to make it a left outer join. Thanks everyone for their help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply