sql - 使用父列时如何查找匹配值？(How to find matching values when working with parent column?)

Question

I have the following tables:

(我有以下表格：)

articles:

(文章：)

article_id category_id
1             10-1
2             20-1
3             NULL

categories:

(类别：)

category_id category_name parent_id
10-1             A            00-00
20-1             B            10-1

This works in a way that each article is associated to a category.

(这样可以使每个文章都与一个类别相关联。)

Categories can can have max 2 nesting levels (Eg category & sub category).

(类别最多可以有2个嵌套级别（例如，类别和子类别）。)

The category_id will be the deepest nesting

(category_id将是最深的嵌套)

I want to convert this into:

(我想将其转换为：)

article_id category_id category_name subcategory_name
1            10-1          A           NULL
2            20-1          A              B
3            NULL        NULL          NULL

I'm using PrestoSQL but I think this can be solved with native sql.

(我正在使用PrestoSQL，但我认为可以使用本机sql解决。)

  ask by HaloKu translate from so

Answer 1

With ANSI SQL:

(使用ANSI SQL：)

select 
    art.article_id, 
    art.category_id, 
    coalesce(pcat.category_name, cat.category_name) category_name, 
    case
        when pcat.category_id is null then null 
        else cat.category_name
    end subcategory_name
from articles art
left join categories cat
on art.category_id=cat.category_id
left join categories pcat
on cat.parent_id=pcat.category_id
order by article_id