Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2010
    Posts
    2

    Unanswered: iteration checking

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Google for "recursive SQL" - you will find plenty of examples. You can also check DB2 SQL Cookbook

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •