Thread: List of Categories under a Category

Can any one Help me to Get a List of all Categories under a Category with the mapping.

Code:

SELECT		CategoryID, 
			Name, 
			ParentCategoryID
			
FROM        dbo.Category

where		ParentCategoryID='1000' and
			Deleted='0' and
			Published='1'

the above query just brings up the one level Categories,
But i would like to get all the categories nested under this main category