sql or hql query to find all parents of a particular record 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
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 &gt; 0 DO
        SELECT IFNULL(parent,-1) INTO ch FROM
        (SELECT parent FROM mytable WHERE id = ch) A;
        IF ch &gt; 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&gt; drop database if exists mydb;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

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

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

mysql&gt;


Create the Stored Function

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

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

mysql&gt; DELIMITER ;


Call GetAncestry

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

mysql&gt;


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.