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 (E.g category & sub category). The category_id will be the deepest nesting

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.

Answer 1

With 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