Best guess:
SELECT prod.dep_ID, dep.dep_Name, cat.cat_Description, COUNT(prod.prod_Description) AS CountOfProductsInCategory
FROM tblProduct AS prod INNER|LEFT|RIGHT JOIN appCategory AS cat ON ...
INNER|LEFT|RIGHT JOIN tblDepartment AS dep ON prod.dep_ID = dep.dep_ID
WHERE prod.dep_ID = @dep_ID
GROUP BY prod.dep_ID, dep.dep_Name, cat.cat_Description
Note: i'm using joins to join data into single result set. There are few ways to join data. Please read this to find out which one is the best for you:
Visual Representation of SQL Joins[
^].