Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Mar 2013
    Posts
    39
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Help with left join and inner join

    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.

    PHP Code:
    SELECT cat.cat_namecat.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

    PHP Code:
    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 

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,720
    Thanks
    80
    Thanked 4,514 Times in 4,478 Posts
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Mar 2013
    Posts
    39
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I solved it.

    PHP Code:
    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 


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •