Jeff Moden (9/16/2009)

---

Matt is certainly on the right track... but you don't need a CASE statement...

select A.grading, ISNULL(NULLIF(count(B.IDno),0),COUNT(B.IDno)) as myCount

From A

left join B on A.fkIDno = B.IDno and A.grading = 1

left join C on A.fkIDno = C.IDno and A.grading = 2

Where A.grading > 3

As a sidebar, I used ISNULL instead of COALESCE because ISNULL is a bit faster across a million rows. If you believe in the MYTH of code portability, then you should change it back to COALESCE. 😛