How to return 2 COUNT()

I have this table :

forum_categories

• id
• title

forum_topics

• id
• category_id (n-1 with forum_categories.id)

forum_messages

• id
• topic_id (n-1 with forum_topics.id)
• first

I'd like to return forum_categories.title , count(forum_topics.id) (I mean number of topic for that category) , count(forum_messages.id) (I mean number of messages for this topic where the field first is 0).

This is an example :

forum_categories            forum_topics            forum_messages
1   title1                  1   1                   1       1       0
2   title2                  2   2                   2       2       1
3   title3                  3   1                   3       3       1
                            4   1                   4       6       1
                            5   3                   5       7       0
                            6   3                   6       2       0
                            7   2                   7       1       1
                                                    8       3       0
                                                    9       5       0
                                                    10      7       1
                                                    11      5       1
                                                    12      1       0

The ouput must be :

title1      3       3 (topic 1=2 + topic 3=1 topic4=0
title2      2       2 (topic2=1 + topic7=1)
title3      2       1  (topic5=1 + topic6=0)

Tried somethings like :

SELECT forum_categories.description, 
       COUNT(forum_topics.id), 
       COUNT(forum_messages.id)
  FROM forum_categories 
  JOIN forum_topics 
  JOIN forum_messages ON forum_categories.id = forum_topics.category_id 
                     AND forum_topics.id = forum_messages.topic_id
GROUP BY forum_categories.id, forum_messages.id
ORDER BY forum_categories.date

But its absolutely far away from my target (and also I don't know how to check the *forum_messages.first* field! Any helps?

EDIT

Thanks to Jim Rubenstein this is a partial solution :

SELECT fc.descr开发者_如何学运维iption, COUNT(DISTINCT ft.id) topics, COUNT(fm.id) 
FROM forum_categories fc 
JOIN forum_topics ft ON ft.category_id = fc.id 
JOIN forum_messages fm ON fm.topic_id = ft.id
GROUP BY fc.id ORDER BY fc.date 

The problem of this query is that count every message from forum_messages, but I need to count these message for each topic only when field=0 .

EDIT 2

I think I've found the solution :

SELECT fc.title, COUNT(DISTINCT ft.id) topics, COUNT(fm.id) 
FROM forum_categories fc 
JOIN forum_topics ft ON ft.category_id = fc.id 
LEFT OUTER JOIN (SELECT id, topic_id, first FROM forum_messages WHERE first=0) fm ON fm.topic_id = ft.id
GROUP BY fc.id ORDER BY fc.date 

What do you think about? Can I better it?

You should be able to accomplish this with the DISTINCT keyword. You also want to GROUP BY the category_id so you get a count for topics/messages for each category.

SELECT fc.description, COUNT(DISTINCT ft.id) topics, COUNT(fm.id) messages
FROM forum_categories fc
JOIN forum_topics ft ON ft.category_id = fc.id
JOIN form_messages fm ON fm.topic_id = ft.id
WHERE fm.first = 0
GROUP BY fc.id
ORDER BY fc.date

another option:

SELECT fc.description, COUNT(DISTINCT ft.id) topics, SUM(CASE WHEn fm.first = 0 THEN 1 ELSE 0) messages
FROM forum_categories fc
JOIN forum_topics ft ON ft.category_id = fc.id
JOIN form_messages fm ON fm.topic_id = ft.id
WHERE fm.first = 0
GROUP BY fc.id
ORDER BY fc.date

note: I aliased your tables to shorter names...because i'm too lazy to type the full names over and over. haha.

You can use subqueries like:

 select (select count(*) from ...., select count(*) from ....)