I think that you're trying to make this more complicated than it needs to be. I was able to match your output with a simple ORDER BY clause.
SELECT Parent_ID, Product_ID, Product_Order
FROM Dept_Product AS p
INNER JOIN Dept AS d
ON p.Dept_ID = d.Dept_ID
ORDER BY d.Parent_ID, d.Dept_ID, p.Product_Order
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA