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(
LEFT JOIN Product p
ON s.SubcateID = p.SubcateID
GROUP BY s.subcate_Name
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.