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

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
..

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"

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".

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

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.

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".

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

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.

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".

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_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 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.