Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL recursive query procedure

Posted on 2009-05-08
4
Medium Priority
?
663 Views
Last Modified: 2012-05-06
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


   
0
Comment
Question by:angelsky
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 43

Expert Comment

by:pcelba
ID: 24335411
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
 
LVL 43

Expert Comment

by:pcelba
ID: 24335511
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
 
LVL 19

Accepted Solution

by:
NerdsOfTech earned 800 total points
ID: 24469389
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question