Skip to content
Advertisement

Names of nodes at depth d for every descendant leaf

I have a category hierarchy that products are attached to. That category hierarchy is saved as an adjacency list. Products can be attached to any category nodes at any level. The category hierarchy is a tree.

I would like to…

  1. get the name of every level 3 category…
  2. per product…
  3. where that product is attached to any level 3 category node…
  4. or a descendant of a level 3 node.

I know I can materialize the hierarchy, and from that I’ve been able to satisfy all requirements but the last. I always lose some products or categories.

Given

I would like to end up with something like

but the closest I have gotten is

  • The order of categories is irrelevent (I want a set, not a list).
  • I can tolerate duplicate categories far better than missing categories or products.
  • I have some liberty to adjust the schema.

Advertisement

Answer

step-by-step demo:db<>fiddle

  1. This CASE clause stores the current name if and only if it is level 3. If it is less, than it returns NULL, if it is greater, it takes the level 3 value.
  2. DISTINCT is allowed in GROUP BY aggregates to eliminate non-distinct values.
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement