CODEWITHSUNDEEP
mysqlsqljoinopencart
thelaw
thelaw

Reputation: 385

MySQL join products with category tree even if categories are null

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

Upvotes: 1

Views: 451

Answers (3)

alex4ip
alex4ip

Reputation: 1

Code beforehand have a little bug: 'cat 2' cd2 have same name with 'cat 1' cd because in 8 line forgot 2 after first c betters write c2.category_id = cd2.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
LEFT join oc_category c on pc.category_id = c.category_id
LEFT join oc_category_description cd on c.category_id = cd.category_id
LEFT join oc_category c2 on (c.parent_id = c2.category_id)
LEFT join oc_category_description cd2 on c2.category_id = cd2.category_id
LEFT join oc_category c3 on (c2.parent_id = c3.category_id)
LEFT join oc_category_description cd3 on c3.category_id = cd3.category_id 
where 
p.status = 1

Upvotes: 0

Rakib
Rakib

Reputation: 643

Please use left join instead of join I guess

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
LEFT join oc_category c on pc.category_id = c.category_id
LEFT join oc_category_description cd on c.category_id = cd.category_id
LEFT join oc_category c2 on (c.parent_id = c2.category_id)
LEFT join oc_category_description cd2 on c.category_id = cd2.category_id
LEFT join oc_category c3 on (c2.parent_id = c3.category_id)
LEFT join oc_category_description cd3 on c3.category_id = cd3.category_id 
where 
p.status = 1

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13026

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)

Upvotes: 0

Related Questions