MySQL recursive query procedure

I have a COMPARISONS table that has the following schema:
COMPARISONS
patch  (int)
replacement (int)
flag (int)  1(true) 0 (false)

For example:
patch   replacement   flag
1   2   0
2   3   0
3   7   1
4   7   1
5   7   1
6   8   1
7   8   1


I would like to know my descendant patches (comparison flag must be == 1).  
So if I enter 8,  it will display 3,4,5,6,7.  The depth of the tree is not limited.

Would need your help in creating a procedure or SQL statement for this in MySQL


   
angelskyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
NerdsOfTechConnect With a Mentor Technology ScientistCommented:
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html

Here is an example of a similar procedure yours can conform to.
DROP PROCEDURE IF EXISTS ListReached;
DELIMITER |
 
CREATE PROCEDURE ListReached( IN root CHAR(1) )
BEGIN
  DECLARE rows SMALLINT DEFAULT 0;
  DROP TABLE IF EXISTS reached;
  CREATE TABLE reached (
    nodeID CHAR(1) PRIMARY KEY
  ) ENGINE=HEAP;
  INSERT INTO reached VALUES (root );
  SET rows = ROW_COUNT();
  WHILE rows > 0 DO
    INSERT IGNORE INTO reached
      SELECT DISTINCT childID
      FROM edges AS e
      INNER JOIN reached AS p ON e.parentID = p.nodeID;
    SET rows = ROW_COUNT();
    INSERT IGNORE INTO reached
      SELECT DISTINCT parentID
      FROM edges AS e
      INNER JOIN reached AS p ON e.childID = p.nodeID;
    SET rows = rows + ROW_COUNT();
  END WHILE;
  SELECT * FROM reached;
  DROP TABLE reached;
END;
|
DELIMITER ;
CALL ListReached('A');

Open in new window

0
 
pcelbaCommented:
Somehow I don't see recursivity from your example because you can easily write

SELECT patch
  FROM COMPARISONS
 WHERE replacement <= 8 AND flag = 1
 ORDER BY patch

0
 
pcelbaCommented:
If you need all patches on one line then GROUP_CONCAT() could work (I am not sure because I don't have MySQL by hand):  

SELECT GROUP_CONCAT(patch) As Patches
  FROM COMPARISONS
 WHERE replacement <= 8 AND flag = 1
0
All Courses

From novice to tech pro — start learning today.