mallen Posted October 2, 2013 Share Posted October 2, 2013 I have a working query that displays a list of categories, sub categories. I also have a working query that only lists main categories but leaves out "this company =1" and the products table. Incorporating this line is what I am have hard time with.This first one works, and there are no sub categories to display. SELECT cat.cat_name, cat.cat_id FROM categories as cat LEFT JOIN category_assoc as assoc ON assoc.cat_id = cat.cat_id LEFT JOIN products as pr ON pr.prod_id = assoc.prod_id WHERE `". $this->company . "` = '1' ORDER BY cat.cat_name ASC"; This one displays categories and sub categories, but also ones with no products associated with them for that company. I don't want any categories to be listed if there are no products for that company. The condition for the company being "1" is what determines the list of categories. So each of the companies don't have the same list.I think I need a inner join? I have categories, category_assoc and products tables. SELECT main_categories.cat_id AS main_cat_id , main_categories.cat_name AS main_cat_name , child_categories.cat_id AS child_cat_id , child_categories.cat_name AS child_cat_name FROM categories AS main_categories LEFT JOIN categories AS child_categories ON child_categories.cat_parent = main_categories.cat_id WHERE main_categories.cat_parent IS NULL ORDER BY main_categories.cat_name ASC Quote Link to comment https://forums.phpfreaks.com/topic/282643-help-with-this-select-query/ Share on other sites More sharing options...
Solution mallen Posted October 3, 2013 Author Solution Share Posted October 3, 2013 I solved it. SELECT DISTINCT main_categories.cat_id AS main_cat_id , main_categories.cat_name AS main_cat_name , child_categories.cat_id AS child_cat_id , child_categories.cat_name AS child_cat_name FROM categories AS main_categories LEFT JOIN categories AS child_categories ON child_categories.cat_parent = main_categories.cat_id LEFT JOIN category_assoc AS assoc ON assoc.cat_id = main_categories.cat_id LEFT JOIN products AS pr ON pr.prod_id = assoc.prod_id WHERE main_categories.cat_parent IS NULL AND `". $this->company . "` = '1' ORDER BY main_categories.cat_name ASC Quote Link to comment https://forums.phpfreaks.com/topic/282643-help-with-this-select-query/#findComment-1452420 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.