MySQL 将产品与类别树连接起来，即使类别为空

Question

我想从 Open Cart 数据库中导出所有产品及其父类别。我的表是:

oc_product_description(产品 ID、名称、语言....)

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

oc_product_to_category(产品 ID、类别 ID)

oc_category(category_id，状态....)

oc_category_description(类别 ID、名称、语言 ID ...)

到目前为止，这是我的代码:

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)

但问题是它不会返回只有一个或两个级别类别的产品(c.parent_id = 0 和/或 c2.parent_id = 0)

更新

我最终导出了 3 个不同的文件，然后将它们合并...第一个文件包含所有 3 个级别的类别，第二个文件包含 2 个级别的类别，第三个文件只有一个级别的类别

Answer 1

使它为空的是您的第 3 级类别。加入时为您的第 3 级类别添加条件。

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

完成查询

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)