loop in recursive sql query

rahul sharma · #1 (**permalink**) 02-11-10, 14:49

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

n_i · #2 (**permalink**) 02-11-10, 14:54

Why do you cross-post?

SQL Recursive Call

rahul sharma · #3 (**permalink**) 02-11-10, 15:01

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]

tonkuma · #4 (**permalink**) 02-11-10, 15:57

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.

Lenny77 · #5 (**permalink**) 02-11-10, 16:35

Quote:

Originally Posted by tonkuma

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

Internet Services

Web Development

Digital Marketing

Consumer Tech