MySQL – Select Union of Rows Depending on Previous Row

MySQLselectunion

I have a table in MySQL that looks like this:

CREATE TABLE IF NOT EXISTS `mytable` (
`id` int(11) NOT NULL,
  `data` VARCHAR(255),
  `otherid` int(11),
  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

With some rows:

ID|STUFF|OTHERID
-----------------
1 |"stuff"| 2
2 |  "x"  | 4
3 |  "y"  | NULL
4 |  "z"  | NULL

I want to select first the row where data = "stuff" and then all the following rows (which could be "infinite") where the next rows are found by id = lastrow.otherid. Like this:

ID|STUFF|OTHERID
-----------------
1 |"stuff"| 2
2 |  "x"  | 4
4 |  "z"  | NULL

If there is no row with data = something, then no rows should be selected.

Any suggestions?

Best Answer

About 4.5 years ago, I wrote a rather aggressive stored procedure to traverse data stored in a hierarchy and bring back all descendants within that table : Find highest level of a hierarchical field: with vs without CTEs

I took the code from the GetFamilyTree function and wrote it as a procedure for you

STORED PROCEDURE

DELIMITER $$
DROP PROCEDURE IF EXISTS GetDataFromData $$
CREATE PROCEDURE GetDataFromData (GivenData VARCHAR(255))
ThisStoredProcedure:BEGIN

    DECLARE rv,q,queue,queue_children VARCHAR(1024);
    DECLARE queue_length,front_id,pos INT;

    SET rv = '';

    DROP TABLE IF EXISTS DataFromData;
    CREATE TEMPORARY TABLE DataFromData LIKE mytable;

    SELECT COUNT(1) INTO @FoundCount FROM mytable WHERE data = GivenData;

    IF @FoundCount = 1 THEN
        SELECT id INTO @FoundID FROM mytable WHERE data = GivenData;

        SET rv = @FoundID;
        SET queue = @FoundID;
        SET queue_length = 1;

        WHILE queue_length > 0 DO
            SET front_id = FORMAT(queue,0);
            IF queue_length = 1 THEN
                SET queue = '';
            ELSE
                SET pos = LOCATE(',',queue) + 1;
                SET q = SUBSTR(queue,pos);
                SET queue = q;
            END IF;
            SET queue_length = queue_length - 1;

            SELECT IFNULL(qc,'') INTO queue_children FROM
            (SELECT GROUP_CONCAT(otherid) qc FROM mytable WHERE id = front_id) A;

            IF LENGTH(queue_children) = 0 THEN
                IF LENGTH(queue) = 0 THEN
                    SET queue_length = 0;
                END IF;
            ELSE
                IF LENGTH(rv) = 0 THEN
                    SET rv = queue_children;
                ELSE
                    SET rv = CONCAT(rv,',',queue_children);
                END IF;
                IF LENGTH(queue) = 0 THEN
                    SET queue = queue_children;
                ELSE
                    SET queue = CONCAT(queue,',',queue_children);
                END IF;
                SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
            END IF;
        END WHILE;

        SET @sql = CONCAT('INSERT INTO DataFromData SELECT * FROM mytable WHERE id in (',rv,')');
        PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
    END IF;

    SELECT * FROM DataFromData;

END $$
DELIMITER ;

SAMPLE DATA FROM YOUR QUESTION

DROP DATABASE IF EXISTS magistermundus;
CREATE DATABASE magistermundus;
USE magistermundus
CREATE TABLE IF NOT EXISTS `mytable` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `data` VARCHAR(255),
    `otherid` int(11),
    PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO mytable (data,otherid) VALUES ('stuff',2);
INSERT INTO mytable (data,otherid) VALUES ('x',4);
INSERT INTO mytable (data,otherid) VALUES ('y',NULL);
INSERT INTO mytable (data,otherid) VALUES ('z',NULL);
SELECT * FROM mytable;

SAMPLE DATA FROM YOUR QUESTION LOADED

mysql> DROP DATABASE IF EXISTS magistermundus;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE DATABASE magistermundus;
Query OK, 1 row affected (0.01 sec)

mysql> USE magistermundus
Database changed
mysql> CREATE TABLE IF NOT EXISTS `mytable` (
    ->     `id` int(11) NOT NULL AUTO_INCREMENT,
    ->     `data` VARCHAR(255),
    ->     `otherid` int(11),
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('stuff',2);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('x',4);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('y',NULL);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('z',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM mytable;
+----+-------+---------+
| id | data  | otherid |
+----+-------+---------+
|  1 | stuff |       2 |
|  2 | x     |       4 |
|  3 | y     |    NULL |
|  4 | z     |    NULL |
+----+-------+---------+
4 rows in set (0.00 sec)

mysql>

STORED PROCEDURE EXECUTED

mysql> CALL GetDataFromData('stuff');
+----+-------+---------+
| id | data  | otherid |
+----+-------+---------+
|  1 | stuff |       2 |
|  2 | x     |       4 |
|  4 | z     |    NULL |
+----+-------+---------+
3 rows in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql>

CAVEAT # 1

Writing this using UNION SELECT was not practical since the depth of the tree of data is not known. This code will handle any depth of data.

CAVEAT # 2

This code does not handle circular references in data (Where (id,otherid) and (otherid,id) coexist in this table).

Related Question