MySQL join products with category tree even if categories are null

Question

I want to export all products from an Open Cart database with their parent categories. My tables are:

oc_product_description (product_id, name, language ....)

oc_product (product_id, model, price, status ...)

oc_product_to_category (product_id, category_id)

oc_category (category_id, status ....)

oc_category_description (category_id, name, language_id ...)

Here is my code so far:

select pd.name as 'product', cd3.name as 'cat 1', cd2.name as 'cat 2', cd.name as 'cat 3', p.model, p.price
from oc_product_description pd 
join oc_product p on pd.product_id = p.product_id
join oc_product_to_category pc on p.product_id = pc.product_id
join oc_category c on pc.category_id = c.category_id
join oc_category_description cd on c.category_id = cd.category_id
join oc_category c2 on (c.parent_id = c2.category_id)
join oc_category_description cd2 on c.category_id = cd2.category_id
join oc_category c3 on (c2.parent_id = c3.category_id)
join oc_category_description cd3 on c3.category_id = cd3.category_id 
where 
p.status = 1 AND pd.language_id = 2 AND c.status = 1 AND cd.language_id = 2 
AND ((c2.status = 1 AND cd2.language_id = 2) OR c.parent_id = 0) 
AND ((c3.status = 1 AND cd3.language_id = 2) OR c2.parent_id = 0)

But the problem is that it doesn't return products with only one or two level categories (c.parent_id = 0 and/or c2.parent_id = 0)

UPDATE

I ended up exporting 3 different files and then merge them... 1st file with all 3 level of categories, 2nd file with 2 level of categories and 3rd file with only one level of category

Zero is not NULL. PS Please in code questions give a minimal reproducible example--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS/product & DDL, which includes constraints & indexes & tabular formatted initialization. — philipxy

Ed Bangga Ed Bangga · Accepted Answer · 2019-10-11T08:37:31

what's making it null is your 3rd level category. Add condition to your 3rd level category when joining.

join oc_category c3 on (c2.parent_id = c3.category_id and c3.category_id != c.category_id)

complete query

select pd.name as 'product', cd3.name as 'cat 1', cd2.name as 'cat 2', cd.name as 'cat 3', p.model, p.price
from oc_product_description pd 
join oc_product p on pd.product_id = p.product_id
join oc_product_to_category pc on p.product_id = pc.product_id
join oc_category c on pc.category_id = c.category_id
join oc_category_description cd on c.category_id = cd.category_id
join oc_category c2 on (c.parent_id = c2.category_id and c2.category_id != c.category_id)
join oc_category_description cd2 on c.category_id = cd2.category_id
join oc_category c3 on (c2.parent_id = c3.category_id and c2.category_id != c.category_id and c3.category_id != c.category_id)
join oc_category_description cd3 on c3.category_id = cd3.category_id 
where p.status = 1 AND pd.language_id = 2 AND c.status = 1 AND cd.language_id = 2 AND 
    ((c2.status = 1 AND cd2.language_id = 2) OR c.parent_id IS NULL) 
    AND ((c3.status = 1 AND cd3.language_id = 2) OR c2.parent_id IS NULL)

MySQL join products with category tree even if categories are null

2 Answers