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

    Red face Unanswered: SQL Recursive Call

    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
    Last edited by rahul sharma; 02-11-10 at 15:13. Reason: better understanding

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You have to limit the level of recursion somehow. Try
    Code:
    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 
      AND a.iteration < 100
    )
    SELECT Item ,Clone,iteration
    FROM temp_item
    Use the limit that makes sense in your situation.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2010
    Posts
    4
    Thanq but wot if am not sure about the number of clones....

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by rahul sharma View Post
    Thanq but wot if am not sure
    Sorry, I don't speak this language. Could you please rephrase your question?
    ---
    "It does not work" is not a valid problem statement.

Tags for this Thread

Posting Permissions

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