MySQL: LEFT JOIN .. select everything from table 1, even if it is not in table 2?

Question

I join several tables for selection

If in the 2nd, 3rd, 4th tables there is nothing suitable, I still want to pull the results until the first table has a match. I thought LEFT JOIN did this, but it is not.

Here is the complete request:

SELECT cart_product.*, prod_drop_products.prod_drop_product_name, everlon_sheet.*, cart_product.product_id AS product_id 
FROM cart_product 
LEFT JOIN everlon_sheet ON cart_product.product_id = everlon_sheet.product_id 
LEFT JOIN prod_drop_products ON cart_product.product_id = prod_drop_products.product_id 
LEFT JOIN prod_drop ON prod_drop.prod_drop_id = prod_drop_products.prod_drop_id 
WHERE prod_drop.prod_drop_name = "Carat Weight" AND cart_product.product_brand = "everlon" 
ORDER BY cart_product.product_manufacturer_num

which pulls results 316

Here is a query without unions:

SELECT cart_product.* 
FROM cart_product 
WHERE cart_product.product_brand = "everlon" 
ORDER BY cart_product.product_manufacturer_num

which pulls 362 results

I have a suspicion that this is due to my WHERE clause prod_drop.prod_drop_name = "Carat Weight in JOIN qry. But is there any way to pull what I need in my request above, but still pull everything from the first (the leftmost , cart_product) tables, even if nothing matches other tables?

Thanks!!

Answer 1

, prod_drop.prod_drop_name null

OR prod_drop.prod_drop_name IS NULL .

Answer 2

WHERE prod_drop.prod_drop_name = "Carat Weight" or prod_drop.prod_drop_name is null

Answer 3

Edit

WHERE prod_drop.prod_drop_name = "Carat Weight" AND cart_product.product_brand = "everlon" 

to

WHERE (prod_drop.prod_drop_name = "Carat Weight" OR prod_drop.prod_drop_name IS NULL) AND cart_product.product_brand = "everlon" 

Now the query also returns a match if there is no corresponding value in prod_drop.