#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    392
    Rep Power
    76

    Combine Two Left Joins in Query


    I have this query that works and displays a list of categories. But I need to combine the products table into it. This is to only list categories that have products with a value of "1". Not sure how to include a second LEFT JOIN. My products table is prod_id, company_abc and I need to include WHERE company_abc = '1'

    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. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,663
    Rep Power
    1958
    You add the second LEFT JOIN similar to the first one.
    something like this:
    Code:
    ...
    LEFT JOIN categories AS child_categories
            ON child_categories.cat_parent = main_categories.cat_id
    LEFT JOIN products AS products
            ON products.prod_id = main_categories.prod_id AND products.value = 1
    WHERE main_categories.cat_parent IS NULL 
    ..
    You have to modify it with the correct table names and conditions.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    392
    Rep Power
    76
    Thanks I tried it and changed the table names.

    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
            LEFT JOIN products 
    AS products ON products.prod_id main_categories.prod_id
        
    AND `company_abc` = '1'
        
    WHERE main_categories.cat_parent IS NULL 
    I get an error: "You have an error ...WHERE main_categories.cat_parent IS NULL LIMIT 0, 50' at line 10"
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    You can't have a LEFT JOIN without a condition. This simply makes no sense. Interestingly, you did have it in your original query, and MrFujin also included it, but then somehow it got lost.

    And if the AND part is supposed to belong to the WHERE clause, it has to actually be there and not before it. Currently, it belongs to the join condition.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    392
    Rep Power
    76
    Thanks I didn't catch that I left off that line. Not getting any errors but it is not factoring in the products table. Shouldn't list categories that don't have "1" for company_abc.
    Also its returning only 28 of 50 categories.

    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
            LEFT JOIN products 
    AS products ON products.prod_id main_categories.cat_id
            WHERE 
    `company_abc` = '1'
            
    AND main_categories.cat_parent IS NULL
            ORDER BY main_categories
    .cat_name ASC 
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by mallen
    Shouldn't list categories that don't have "1" for company_abc.
    Your join condition says

    sql Code:
    products.prod_id = main_categories.cat_id

    I'm pretty sure that's not what you want.



    Originally Posted by mallen
    Also its returning only 28 of 50 categories.
    Apart from the problem above: The query will reject all categories without products, because you require company_abc to be 1. Is that what you want? Then remove the "LEFT" from LEFT JOIN products. You're effectlively doing an inner join, because all additional rows caused by the left join are immediately removed due to the company_abc = 1 check. So there's no reason to pretend you're doing a left join. If you do want a left join, then you need to allow company_abc IS NULL as well.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    392
    Rep Power
    76
    Thanks. I changed it to
    = main_categories.prod_id
    also changed LEFT JOIN to INNER JOIN Now it doesn't return any categories.

    PHP Code:
    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
            INNER JOIN products 
    AS products ON products.prod_id main_categories.prod_id
            WHERE 
    `company_abc` = '1'
            
    AND main_categories.cat_parent IS NULL
            ORDER BY main_categories
    .cat_name ASC 
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    A category has exactly one product? That doesn't make a lot of sense to me. Or is this some kind of special product? Then you should actually make that clear in the name (call it something like "top_product" or so).

    Anyway, the great thing about SQL (or tech stuff in general) is that everything happens for a reason. You can actually investigate problems. Do that. Don't wait for us to debug your queries.

    For example, an obvious step would be to join the categories with the products and see if there are in fact categories with company_abc = 1.

    sql Code:
    SELECT
    	main_categories.cat_id
    	, products.company_abc
    FROM
    	categories AS main_categories
    	JOIN
    		products ON products.prod_id = main_categories.prod_id	-- really?
    WHERE
    		main_categories.cat_parent IS NULL
    ORDER BY
    	products.company_abc = 1 DESC
    ;

    Do you actually see results with the company 1?

    By the way, please remove those backtick leftovers from company_abc. You should qualify that name (write a table prefix before it) so that it's clear where it comes from.

    Aliasing a table with the same name ("products AS products") doesn't make a lot of sense either.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by Jacques1
    Anyway, the great thing about SQL (or tech stuff in general) is that everything happens for a reason.
    this is what i like about sql too (but i have also felt the frustration, the exasperation, the befuddlementation that sql bestows, so i try to be kind to noobs)

    the best explanations are the simplest

    wait'll you tell him where company_abc = 1 needs to go in a left outer join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,663
    Rep Power
    1958
    Originally Posted by Jacques1
    Aliasing a table with the same name ("products AS products") doesn't make a lot of sense either.
    That alias was a mistake from my part.

    @mallen

    As Jacques1 implies, you should probably not join the products with main_categories.
    Have you tried to join the products with child_categories?

    Originally Posted by r937
    wait'll you tell him where company_abc = 1 needs to go in a left outer join
    Just me who can't understand the meaning of this?
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by MrFujin
    Just me who can't understand the meaning of this?
    okay, let me explain briefsly

    maincat-LEFT-categ-LEFT-products implies you want the category tree and any products tied to the 2nd level if any

    putting a condition on a products column into the WHERE clause makes the sql behave like an inner join, not an outer join

    putting that condition into the ON clause of the outer join means you still get the entire category tree
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    392
    Rep Power
    76
    WHERE `company_abc` = '1' is part of my query I have used before. I know, I know bad design but someone else did it and I am leaving it for now. I have many products for each company. I have a working query that displays all the categories, sub categories, and products. 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 almost most works but displays categories that have no products associated with them. I don't want any categories to be listed if there are no products.
    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 
    Upon further thought, I changed the categories that don't have products from NULL to "0" on the cat_parent table and now they don't show. 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.
    Last edited by mallen; September 30th, 2013 at 02:22 PM.
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    392
    Rep Power
    76
    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 
  26. #14
  27. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by mallen
    I solved it.
    if `". $this->company . "` represents a column in the products table, then you haven't understood how LEFT OUTER JOINs work

    change them all to INNER JOINs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo