Hi,
I have 2 tables for retriving product details and the details required are catentry_id,catentry_name,catgrp_id,catgrp_name,ca tsubgrp_id,catsubgrp_name and catalog_id.
the hierarchy is catentry->catgrp->catsubgrp->catalog

there are 3 tables to retrive these details..
The main table gives me the catentry_id,catgroup_id and catentry details.
The second table gives relations between parentcatgroup_id and childcatgroup_id and it's catalog_id where these 3 are the keys to this table.
And the last table gives the catgroup_id and catgroup details.


The problem is -there is an iteration between parent-child groups,ie there is a chance that a parent group can come again as a child group and it continues in its hierarchy.
I have to display group details, which should be the last group in the iteration and subgroup details which should be the next second group in this loop.
So how to find the group and subgroup IDs from the relation table for each product? how can i write a query to retrive these details with itration checking between parent,child relation?

example:
table1
-----
catentry_id catgroup_id
1 20
2 30
5 50

table 2
-------
parent_catgroup_id child_catgroup_id catalog_id
30 20 1000
70 30 1002

table3
------
catgroup_id catgroup_name
20 aaa
30 bbb
50 ccc
60 nnn
70 jjj

target table
------------
catentry_id group_id group_name subgroup_id subgroup_name catalog_id
1 70 jjj 30 bbb 1002
2 70 jjj 30 bbb 1002
5 50 ccc NULL NULL NULL

Google for "recursive SQL" - you will find plenty of examples. You can also check DB2 SQL Cookbook