three table outer join with aggregate functions

[jhilty]

here is a query joining three tables (SQL 2000) that doesn't return the results
i need.

SELECT o.OutID, o.GroupID, o.OutTitle, o.SubHead, o.OutOrder,
COUNT(q.QuestionID) AS NumQuestions,
COUNT(f.FlashCardID) AS NumCards
FROM Outline o
LEFT OUTER JOIN Questions q ON q.OutID = o.OutID
LEFT OUTER JOIN Flashcards f ON f.OutID = o.OutID
WHERE o.GroupID = #URL.GroupID#
GROUP BY o.OutID, o.GroupID, o.OutTitle, o.SubHead, o.OutOrder
ORDER BY o.OutOrder

In theory this should give me the info from the Outline table and then count
up the total number of questions and flashcards that are associated with each
Outline item (if any)

I don't get any errors, but the results are not correct for the count's. If
there are 2 questions and 3 flashcards for a given Outline item, both the
NumQuestions and NumCards variable return a 6.

works fine if I only work with one of the tables (Questions or Flashcards) but
not when I try to combine both at once.

I'm stumped - any ideas?

[jhilty]

so here's one solution - adding DISTINT to the count aggregate functions seems to work

COUNT(DISTINCT(q.QuestionID)) AS NumQuestions,
COUNT(DISTINCT(f.FlashCardID)) AS NumCards

[Unregistered]

Thank you very much! I've been trying to solve this similar problem with no luck for the past 2 days!

Now I can finally move on with my project balance sheet!