Nested joints on a set of naive trees

Question

Assuming I have something along the lines of this example:

CREATE TABLE NaiveTable { id BIGINT NOT NULL, parentId BIGINT NULL, name VARCHAR(20) NULL, CONSTRAINT hierarchy FOREIGN KEY (parentId) REFERENCES NaiveTable(id) PRIMARY KEY (id) } 

As a note, parentId is a reference to the id from NaiveTable (in case I missed the exact syntax).

The data is somewhere along the lines of these

 +---------+----------+----------+ | id | parentId | name | +---------+----------+----------+ | 1 | null | node1 | +---------+----------+----------+ | 2 | 1 | node2 | +---------+----------+----------+ | 3 | 1 | node3 | +---------+----------+----------+ | 4 | 2 | node4 | +---------+----------+----------+ 

The column name contains some unrealized labels. I am looking for a way to build a SQL query in a MySQL table, where all the information will be smoothed and sorted by hierarchy as follows:

 node 1, depth 0 node 2, depth 1 node 4, depth 2 node 3, depth 1 

NOTE. I just can't change the database schema. I can only create SQL queries. Also, I cannot use the WITH keyword, since MySQL does not support it. Is there any way to make such a request? However, any solution for a depth of two or more is considered quite good .

EDIT: SQL script if you like to experiment :)

Answer 1

If the database schema is fixed and you cannot add / edit any table, all you can do is build a tree in memory (in some programming language), and then try to calculate the depth of the node in memory. Therefore, I reply that you cannot create the desired result with a single request!

But if you can change the schema of your database, you can check this out.

Answer 2

Here is a small recursive stored procedure that should work at any depth. This is my first stored procedure, so please let me know how to improve it.

 DROP PROCEDURE IF EXISTS tree_reader; DELIMITER $$ CREATE PROCEDURE tree_reader(IN parent INT, IN depth INT) READS SQL DATA BEGIN DECLARE id_val INT; DECLARE name_val VARCHAR(255); DECLARE _table_name VARCHAR(255); DECLARE no_more_rows BOOLEAN; DECLARE cur CURSOR FOR SELECT id, name FROM tree WHERE IF(parent IS NULL, parentid IS NULL, parentid = parent); DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE; -- create temporary table on outer call only -- IF depth=0 THEN DROP TABLE IF EXISTS _tree; CREATE TEMPORARY TABLE _tree (id INT, name VARCHAR(255), depth INT); END IF; OPEN cur; -- loop with recursion -- tree_loop: LOOP FETCH cur INTO id_val, name_val; IF no_more_rows THEN LEAVE tree_loop; END IF; INSERT INTO _tree VALUES (id_val, name_val, depth); CALL tree_reader(id_val, depth + 1); END LOOP tree_loop; CLOSE cur; -- output results on outer call only -- IF depth=0 THEN SELECT * FROM _tree; DROP TABLE _tree; END IF; END $$ DELIMITER ; 

A few notes:

• Call the procedure: CALL tree_reader(NULL, 0);

• You can use any parent node id, but the second argument is always 0 . In practice, I would probably add a wrapper procedure that takes no arguments and gives a whole tree.

• You must set the recursion limit to work: SET max_sp_recursion_depth = 6; (I chose 6 arbitrarily).