>I have a list of categories. Some of them have sub categories. When I select the categories, I need to include a count field which would indicate either 0, if there is no sub category for that category, or a count indicating how many sub categories that category has. I am looking for the most effective simple SQL syntax for that.

Something like

SELECT COUNT(DISTINCT SubCategoryID) FROM Whatever WHERE CategoryID = 123 ....