OK....I started to reply, but Boris' 2nd answer should do the trick.
Bottom line -
- Don't use a straight LEFT JOIN against two tables where you have multiple rows per key on both sides - you'll get more enhanced-aggregation than Britney Spear's cup size in 1999.
- Avoid correlated subqueries - they look nice and readable, but they are as inefficient as, well, Britney's legal defense. Derived tables (or even CTEs in SQL 05) will usually perform much better.
Kevin