SQL or HQL Query – How to Find All Parents of a Particular Record

cteMySQLoracleoracle-sql-developer

enter image description here

I need a sql or hql query to find all parents of a particular record, that should work in oracle as well as mysql, for eg if I have the id: 201, the parents would be: 188, 187, 1.
any help would be greatly appreciated, thanks

Best Answer

On Oct 24, 2011, I wrote some Stored Functions to perform Ancestor and Descendant traversals (Find highest level of a hierarchical field: with vs without CTEs ). From my post, you would use GetAncestry. From your data, the code in GetAncestry will traverse until it sees -1 as a parent id. No changes in the algorithm are needed except to change parent_id to parent.

For the sake of example, let's suppose the table is called mydb.mytable

GetAncestry Code

USE mydb
DELIMITER $$
DROP FUNCTION IF EXISTS `GetAncestry` $$
CREATE FUNCTION `GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
    DECLARE rv VARCHAR(1024);
    DECLARE cm CHAR(1);
    DECLARE ch INT;

    SET rv = '';
    SET cm = '';
    SET ch = GivenID;
    WHILE ch > 0 DO
        SELECT IFNULL(parent,-1) INTO ch FROM
        (SELECT parent FROM mytable WHERE id = ch) A;
        IF ch > 0 THEN
            SET rv = CONCAT(rv,cm,ch);
            SET cm = ',';
        END IF;
    END WHILE;
    RETURN rv;
END $$
DELIMITER ;

Once you create this stored procedure, you would call the function as follows:

USE mydb
SELECT GetAncestry(201);

Let's test that out

Sample Data Based On Your Question

drop database if exists mydb;
create database mydb;
use mydb
create table mytable
(id int not null,
name varchar(20),
parent int default -1,
primary key (id));
insert into mytable values
(1,'ROOT',-1),(162,'ROOT',1),(163,'ROOT',1),(164,'ROOT',1),
(165,'ROOT',1),(166,'ROOT',1),(167,'ROOT',1),(168,'ROOT',1),
(169,'ROOT',1),(170,'ROOT',1),(171,'ROOT',1),(172,'ROOT',1),
(173,'ROOT',1),(174,'ROOT',1),(175,'ROOT',1),(176,'ROOT',1),
(177,'ROOT',1),(178,'ROOT',1),(179,'ROOT',1),(180,'ROOT',1),
(181,'ROOT',1),(182,'ROOT',1),(183,'ROOT',1),(184,'ROOT',1),
(185,'ROOT',184),(186,'ROOT',1),(187,'ROOT',1),(188,'ROOT',187),
(201,'ROOT',188),(202,'ROOT',201),(203,'ROOT',202);

Load Sample Data

mysql> drop database if exists mydb;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create database mydb;
Query OK, 1 row affected (0.00 sec)

mysql> use mydb
Database changed
mysql> create table mytable
    -> (id int not null,
    -> name varchar(20),
    -> parent int default -1,
    -> primary key (id));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into mytable values
    -> (1,'ROOT',-1),(162,'ROOT',1),(163,'ROOT',1),(164,'ROOT',1),
    -> (165,'ROOT',1),(166,'ROOT',1),(167,'ROOT',1),(168,'ROOT',1),
    -> (169,'ROOT',1),(170,'ROOT',1),(171,'ROOT',1),(172,'ROOT',1),
    -> (173,'ROOT',1),(174,'ROOT',1),(175,'ROOT',1),(176,'ROOT',1),
    -> (177,'ROOT',1),(178,'ROOT',1),(179,'ROOT',1),(180,'ROOT',1),
    -> (181,'ROOT',1),(182,'ROOT',1),(183,'ROOT',1),(184,'ROOT',1),
    -> (185,'ROOT',184),(186,'ROOT',1),(187,'ROOT',1),(188,'ROOT',187),
    -> (201,'ROOT',188),(202,'ROOT',201),(203,'ROOT',202);
Query OK, 31 rows affected (0.01 sec)
Records: 31  Duplicates: 0  Warnings: 0

mysql>

Create the Stored Function

mysql> USE mydb
Database changed
mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS `GetAncestry` $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE FUNCTION `GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
    -> DETERMINISTIC
    -> BEGIN
    ->     DECLARE rv VARCHAR(1024);
    ->     DECLARE cm CHAR(1);
    ->     DECLARE ch INT;
    ->
    ->     SET rv = '';
    ->     SET cm = '';
    ->     SET ch = GivenID;
    ->     WHILE ch > 0 DO
    ->         SELECT IFNULL(parent,-1) INTO ch FROM
    ->         (SELECT parent FROM mytable WHERE id = ch) A;
    ->         IF ch > 0 THEN
    ->             SET rv = CONCAT(rv,cm,ch);
    ->             SET cm = ',';
    ->         END IF;
    ->     END WHILE;
    ->     RETURN rv;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

Call GetAncestry

mysql> SELECT GetAncestry(201);
+------------------+
| GetAncestry(201) |
+------------------+
| 188,187,1        |
+------------------+
1 row in set (0.00 sec)

mysql>

Only one thing left to do...

GIVE IT A TRY !!!

I have discussed using GetAncestry in the DBA StackExchange many times

Please note that this is a MySQL-specific answer. Please read PL/SQL Documentation if you wish to implement the algorithm in a PL/SQL block or a Stored Procedure.

Related Question