I'm not entirely sure I completely understand what it is you are trying to do here, but I
think you are losing results where you don't have data?
This may be of use to you?
CREATE PROC schemaname.procsname(
@SubCateID INT
) AS
BEGIN
SELECT s.subcate_name,
SUM(ISNULL(p.p_Qty,0))TotalQty
FROM
LEFT JOIN Product p
ON s.SubcateID = p.SubcateID
GROUP BY s.subcate_Name
END
The left join will allow the subcate to show when there is no match at Product.
Let me know if I've got the wrong end of the stick with this.