How to show only categories with products in shopping cart

Question

In shopping cart product category tree is defined in nested set form as

create table  artomlii (
  artomaliik serial primary key,
  treeparent integer references artomlii,  -- parent or null if root node
  categoryname char(50) );

tree maximum depth is 5.

Products in tree are defined as

create table artomadu (
  artomaliik integer primary key references artomlii not null,
  productid char(20) primary key not null
  )

In cart home page root categories are shown using query

select * from artomlii where treeparent is null

Depending on logged-in user, some root categories can be empty, they does not contain any products in any subcategories. for this custom filter is applied to artomadu table.

This query shows empty root categories also. How to fix this so that only root categories having at least one product products in any of its child category are displayed ?

Can WITH RECURSIVE used or other idea ?

Answer 1

with recursive cte as (
    select a.artomaliik, a.categoryname, a.artomaliik as treeparent
    from artomlii as a
    where a.treeparent is null

    union all

    select c.artomaliik, c.categoryname, a.artomaliik as treeparent
    from artomlii as a
        inner join cte as c on c.treeparent = a.treeparent
)
select distinct
    c.artomaliik, c.categoryname
from cte as c
where
    exists (select * from artomadu as a where a.artomaliik = c.treeparent)

sql fiddle demo