Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2010
    Posts
    4

    Question Unanswered: loop in recursive sql query

    Hi

    I have a table tblItems

    Item | Clone
    A B
    B C
    C D
    B A
    C B

    Now i want to find out all the clones of item A
    it should give me

    B
    C
    D

    If i write a SQL recursive call am able to handle the B->A thing but C->B makes me end up in a loop. please see my query below and tell me the way I can prevent the loop.

    WITH temp_item(Item , Clone, iteration) AS
    (
    SELECT Item , Clone, 0
    FROM tblItemsWHERE Item = 'A'

    UNION ALL

    SELECT a.Item , b.Clone, a.iteration + 1
    FROM temp_item AS a, tblItems AS b
    WHERE a.Clone= b.Item
    AND a.Item ='A'
    AND b.Clone!=a.Item
    )
    SELECT Item ,Clone,iteration
    FROM temp_item

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2010
    Posts
    4
    I first posted in sql then found a similar query here.
    so thought there could be ppl who visit just this but not tht.
    I dint want 2 miss them [:P]

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Add a column clone_list instead of Item to the recursive query.

    Here is an example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    tblItems(Item , Clone) AS (
    VALUES
      ('A' , 'B')
    , ('B' , 'C')
    , ('C' , 'D')
    , ('B' , 'A')
    , ('C' , 'B')
    )
    /******************************
    Find all the clones of item A.
    It should give:
      B , C , D
    ******************************/
    ,all_clones(iteration , clone , clone_list) AS (
    SELECT 1 , clone , CAST(item || ' , ' || clone AS VARCHAR(100) )
      FROM tblItems
     WHERE item = 'A'
    UNION ALL
    SELECT iteration + 1
         , new.clone
         , pre.clone_list || ' , ' || new.clone
      FROM all_clones pre
         , tblItems   new
     WHERE iteration < 100
       AND new.item = pre.clone
       AND LOCATE(new.clone , pre.clone_list) = 0
    )
    SELECT clone AS clones
      FROM all_clones
     ORDER BY
           iteration
    ;
    ------------------------------------------------------------------------------
    
    CLONES
    ------
    B     
    C     
    D     
    
      3 record(s) selected.

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up

    Quote Originally Posted by tonkuma View Post
    Add a column clone_list instead of Item to the recursive query.

    Here is an example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    tblItems(Item , Clone) AS (
    VALUES
      ('A' , 'B')
    , ('B' , 'C')
    , ('C' , 'D')
    , ('B' , 'A')
    , ('C' , 'B')
    )
    /******************************
    Find all the clones of item A.
    It should give:
      B , C , D
    ******************************/
    ,all_clones(iteration , clone , clone_list) AS (
    SELECT 1 , clone , CAST(item || ' , ' || clone AS VARCHAR(100) )
      FROM tblItems
     WHERE item = 'A'
    UNION ALL
    SELECT iteration + 1
         , new.clone
         , pre.clone_list || ' , ' || new.clone
      FROM all_clones pre
         , tblItems   new
     WHERE iteration < 100
       AND new.item = pre.clone
       AND LOCATE(new.clone , pre.clone_list) = 0
    )
    SELECT clone AS clones
      FROM all_clones
     ORDER BY
           iteration
    ;
    ------------------------------------------------------------------------------
    
    CLONES
    ------
    B     
    C     
    D     
    
      3 record(s) selected.
    I like this solution !

    Lenny

Posting Permissions

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