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?