Sorry I had a typo... I think it should have been:
SELECT products.*, productcategories.*, productmanufacturers.*, productcategories_2.CategoryName AS parentcategoryname FROM FROM products LEFT JOIN productcategories ON products.ProductCategoryID = productcategories.CategoryID LEFT OUTER JOIN productcategories AS productcategories_2 ON productcategories_2.CategoryID = productcategories.CategoryParentID LEFT JOIN productmanufacturers ON products.ProductManufacturerID = productmanufacturers.ManufacturerID