I was wondering if this query could be improved....
Basically, I have a table containing a product category tree, a table of products, a table of brands and a link table relating a product to any number of products. The products table contains a brand_id as a foreign key.
So when viewing products within a given category, I want the user to be able to filter the results by brand. So I need to select a list of brands that have products within the selected category. Here is what I have:
Can this be made more efficient or is it an acceptable solution?Code:SELECT DISTINCT(b.brand_id), b.brand_name FROM brands AS b INNER JOIN products_to_categories AS ptc ON ptc.category_id = $catid INNER JOIN products AS p ON p.prod_id = ptc.prod_id WHERE b.brand_id = p.brand_id ORDER BY b.brand_name