I have three tables:

• forums with columns: id, label, name
• discussions with columns: id, name, creation_date, open, forum_id, user_id
• messages with columns: id, content, date, updated_date, discussion_id, user_id

I want to get each forum and for each forum get the number of discussions and the number of messages. I currently have the following code:

SELECT "Forum"."id", "Forum"."label", "Forum"."name", COUNT("Discussions"."id") AS "nbDiscussions", COUNT("Discussions->Messages"."id") AS "nbMessages"
FROM "forums" AS "Forum"
LEFT OUTER JOIN "discussions" AS "Discussions" ON "Forum"."id" = "Discussions"."forum_id"
LEFT OUTER JOIN "messages" AS "Discussions->Messages" ON "Discussions"."id" = "Discussions->Messages"."discussion_id"
GROUP BY "Forum"."id"
ORDER BY "Forum"."id";

It almost works except that nbDiscussions have the same value as nbMessages. Why are they the same ? On the result that I have, it looks like nbDiscussions took the value of nbMessages.