sorry, I can't quite get it
here's what i have done..
i am checking for 3 levels of hierarchy, (if there's a way to check for n levels, i would love to know)
knowing the product ID, 35 in this instance i am joining the products table, the productcategories table and the categories table, (3 times) in each case joining each instance of the categories table on Category Parent to CategoryID of the next instance.
I get a result, but I get a duplicate row where the 3 level hierarchy is displayed, but also the last 2 levels are also displayed on a different row.
Am I missing the point ?
SELECT ps3_products.ProductID, ps3_categories_2.CategoryParent AS Cat2Parent, ps3_categories_2.CategoryID AS Cat2ID, ps3_categories_2.CategoryName AS Cat2Name, ps3_categories_1.CategoryParent AS Cat1Parent, ps3_categories_1.CategoryID AS Cat1ID, ps3_categories_1.CategoryName AS Cat1Name, ps3_categories.CategoryParent AS CatParent, ps3_categories.CategoryID AS CatID, ps3_categories.CategoryName AS CatName
FROM ((ps3_products INNER JOIN (ps3_categories INNER JOIN ps3_productcategories ON ps3_categories.CategoryID = ps3_productcategories.CategoryID) ON ps3_products.ProductID = ps3_productcategories.ProductID) INNER JOIN ps3_categories AS ps3_categories_1 ON ps3_categories.CategoryParent = ps3_categories_1.CategoryID) LEFT JOIN ps3_categories AS ps3_categories_2 ON ps3_categories_1.CategoryParent = ps3_categories_2.CategoryID
WHERE (((ps3_products.ProductID)=35));
