2 left joins of the same table

[dan]

i have 2 tables that i want to count

TABLE 1
categories
--id
--name

forum_comments
--id
--category_id
--index (0 for top topic, or comment ID for sub posts)
--title
--comment

I'm trying to list all of the categories w/ a topic count and a posts
count, so I'm trying to join a select of categories with count()s. my
joins are of the same table but they keep coming up the same number?

select
C.id ,
C.name,
count(F.id) as topics,
count(F2.id) as posts
FROM categories as C
LEFT OUTER
JOIN forum_topics as F ON C.id=F.category_id AND F.level=0
LEFT OUTER
JOIN forum_topics as F2 ON C.id=F2.category_id
GROUP BY C.id

Any ideas?
thanks

[dan]

well, no one posted but i found out what i was doing wrong. this id
for anyone who finds this in the future. I just had to ad DISTINCT
clause in my count.

select
C.id ,
C.name,
count(DISTINCT F.id) as topics,
count(DISTINCT F2.id) as posts
FROM categories as C
LEFT
JOIN forum_topics as F ON C.id=F.category_id AND F.level=0
LEFT
JOIN forum_topics as F2 ON C.id=F2.category_id
GROUP BY C.id


ry*****@yahoo.com (dan) wrote in message news:<7b**************************@posting.google. com>...

i have 2 tables that i want to count

TABLE 1
categories
--id
--name

forum_comments
--id
--category_id
--index (0 for top topic, or comment ID for sub posts)
--title
--comment

I'm trying to list all of the categories w/ a topic count and a posts
count, so I'm trying to join a select of categories with count()s. my
joins are of the same table but they keep coming up the same number?

select
C.id ,
C.name,
count(F.id) as topics,
count(F2.id) as posts
FROM categories as C
LEFT OUTER
JOIN forum_topics as F ON C.id=F.category_id AND F.level=0
LEFT OUTER
JOIN forum_topics as F2 ON C.id=F2.category_id
GROUP BY C.id

Any ideas?
thanks