I am working on a site where product brands have been set up as categories. Each product has at least 2 categories, one for brand and others for product categories.
On the page for Brand-X, rather than list the products, I wish to show all the other categories that are associated with Brand-X's products.
What would be the most efficient way to obtain a category list from this structure?
For example:
Product A - Categories [Brand-X, Category-1, Category-2]
Product B - Categories [Brand-X, Category-4]
Product C - Categories [Brand-X, Category-2]
Product D - Categories [Brand-Y, Category-3]
on the Brand-X page, display links to Category-1, Category-2, Category-4
SELECT DISTINCT c2.category_id FROM catalog_category_product c1 INNER JOIN catalog_category_product c2 ON c1.product_id = c2.product_id WHERE c1.category_id = <<Brand-X-id>>
but I do not think this the best way.