Hierarchy denormalization challenge

Databases
1

Hi all,

I’m having a problem denormalizing a relationship table.

Table Structure
parentid childid lvl isleaf
1 1 1 N
1 10 2 N
10 100 3 N
10 101 3 N
100 200 4 Y
101 201 4 Y

The output I am trying to produce is:
leafid lvl1_id lvl2_id lvl3_id lvl4_id
200 1 10 100 null
201 1 10 101 null

When the level is equal to the level of the leaf, the value should be null.
My basic concept is to use a case statement to denormalize the levels (like this)

MAX(CASE WHEN lvl = 1 AND isleaf = 'N' THEN childid ELSE null END) as lvl1_id,
MAX(CASE WHEN lvl = 2 AND isleaf = 'N' THEN childid ELSE null END) as lvl2_id
...

But I am having trouble figuring out the relationship to the leaf node.
I tried

   from hier_table leaf left outer join hier_table node on leaf.parentid = node.childid

but the only level populated is the one before the leaf.

This is in Netezza, which does not yet support recursive queries.

Any help would be greatly appreciated. I am trying to avoid having to do n number of joins to traverse the levels from the leaf up, and am able to create a temp table based on the relationship table if that helps the process.

Thanks,
Ben

2

Have a read of this SitePoint Article on the storage of hierarchical data in a database (http://www.sitepoint.com/hierarchical-data-database/). You may wish to consider migrating over to one of them two methods, my personal preference is for modified pre-order traversal as it means less queries and is quicker.