Thread: Sql query

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    3
    Rep Power
    0

    Sql query


    Hi,

    I'm trying to get different level using hierarchical query in sql.my table is

    item_id child_item_id
    ------------------------------
    p21 p25
    p21 p22
    p22 p23
    p22 p24
    p25 p27
    p25 p26
    p27 p28
    p27 p29
    p30 p31
    p30 p32


    I want to display result with respective levels.
    for example p21 ,p30 are coming under first level .
    p22,p25 ,P31,P32are 2nd level.
    p23,p24,p26,p27 are 3rd level
    p28,p29 are FOURTH level item_id's.

    Already I 'VE tried using CONNECT BY PRIOR clause.BUT STILL I COULDN'T GET THE RESULT.

    PLEASE HELP ME IN THIS.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Nov 2001
    Location
    North West UK
    Posts
    1,147
    Rep Power
    399
    Originally Posted by sugidevshed
    Already I 'VE tried using CONNECT BY PRIOR clause.BUT STILL I COULDN'T GET THE RESULT.
    What are you using for your START WITH clause?

    Oracle8 Code:
     
    SELECT item_id,
           child_item_id,
           LEVEL
      FROM TABLE
     CONNECT BY PRIOR child_item_id = item_id
    START WITH item_id IN ( SELECT item_id 
                    FROM TABLE P
              WHERE NOT EXISTS ( SELECT item_id
                                  FROM TABLE C
                                 WHERE C.child_item_id = P.item_id
                           )
             )


    I prefer to use item_id and parent_id columns rather than item_id and child_id
    Last edited by Mark Baker; January 27th, 2009 at 09:09 AM.
    9 out of 10 PHP problems can be resolved by setting
    PHP Code:
    error_reporting(E_ALL);
    ini_set('display_errors'1); 
    php -l <filename> will identify 9 out of the remaining 10 problems
    Remember, the command line is your friend

    Development Projects:
    PHPExcel
    PHPPowerPoint
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    3
    Rep Power
    0
    thanks Baker....

IMN logo majestic logo threadwatch logo seochat tools logo