Return parent ID in a nested table in sql

Question

this is a table in mysql.

sub_table:

id    name    parent_id
1     sub       0     //it means this the sub has not any parents.
2     sub1      1     //it means the parent is sub
3     sub2      1
4     sub3      3
5     sub4      4
6     sub5      0
7     sub6      6

how can Give an ID number and get its root parent ID?

for example:

if ID=5 return me 1

if ID=6 return me 6

if ID=7 return me 6

SELECT id from table sub_table
WHILE parent_id != 0
BEGIN
    ...?..
END

I don't quite got the q. how come id=5 returns 1 while its parent id is set to 0 do you mean zero = 1, but then how come id =6 returns 6 while its parent is 0 again — Brij Raj Singh - MSFT, Feb 26 '14 at 06:11
@BrijRajSingh : `id` is pk and corresponds to `parent_id` which is fk in same table!! — NoobEditor, Feb 26 '14 at 06:12
@BrijRajSingh: Op wants wants Top level parent,means parent of 5 is 4,parent of 4 is 3,parent of 3 is 1 and there is no parent for 1,so id 5 returns 1 — Cris, Feb 26 '14 at 06:17
`row_number` is what you need here. I'm afraid I can't help you with details — Alexander, Feb 26 '14 at 06:28
@Alexander : `row_number` is not needed here...we don't want result from a particular row, instead, value is to be fetched depending upon the result set!! — NoobEditor, Feb 26 '14 at 06:29
Your current design isn't good for working with ancestors (in fact, with paying attention to your circumstances, your adjacency-tree is an anti-pattern). I suggest you to change your structure. Good choice will be [closure table](http://karwin.blogspot.ru/2010/03/rendering-trees-with-closure-tables.html) concept. Then all routines about dealing with ancestors/descendents will be easily resolved in SQL. — Alma Do, Feb 26 '14 at 07:10
Mostly the same question as http://stackoverflow.com/questions/169817/is-it-possible-to-query-a-tree-structure-table-in-mysql-in-a-single-query-to-an — Dwayne Towell, Feb 26 '14 at 07:13

NoobEditor · Accepted Answer · 2014-02-26T06:59:25.320

1

with PHP, u can achieve this in following way :

    $id = 5;$parent_id = 5; /* set default to values you want*/

    while($parent_id != 0)
    {

       $sql = 'SELECT id,parent_id from sub_table where id = $parent_id ';
       $rs= $mysqli->query($sql);  /* fetch details */
       $old_id = $id; /* this will save the last id in current iteration */
       $parent_id = $rs['parent_id'];
       $id = $rs['id'];

    }

So once coming out of the loop, u will have your result saved in $old_id

edited Feb 26 '14 at 06:59

answered Feb 26 '14 at 06:19

NoobEditor

15,563
19
81
112

@Alexander : u r welcome to post a spicy and interesting answer! – NoobEditor Feb 26 '14 at 06:26
I'm not good enough with `row_number`. That's why I keep silence – Alexander Feb 26 '14 at 06:27
Tnx dear...this code is simple to manage in future...But I wana use it for the first page of my site...for fetch 30-40 records...do you think it reduce performance of the website (speeding and loading)? or I must use a sql function? – user3307827 Feb 26 '14 at 10:13
@user3307827 : as far as i know for 30-40 records, speed and loading wont be a problem..it will be efficient!!! – NoobEditor Feb 26 '14 at 10:18
@user3307827 : if there is not much of updation going on this table, i suggest u create `index` on `parent_id`...that will tune up the performance!! – NoobEditor Feb 26 '14 at 10:20

Hamidreza · Answer 2 · 2014-02-26T10:20:37.787

1

You can use a function like this:

DELIMITER $$ 
CREATE FUNCTION dnmtr_topid(input_id INT UNSIGNED) 
RETURNS INT UNSIGNED 
BEGIN 
DECLARE in_id INT UNSIGNED; 
DECLARE v_pid INT UNSIGNED; 
SET in_id := input_id; 
WHILE in_id > 0 DO 
SET v_pid := in_id; 
SELECT parent_id into in_id FROM dnmtr_all_category WHERE id = in_id LIMIT 1; 
END WHILE; 
RETURN v_pid; 
END $$

then you can use SELECT HATEST(x) to get result.

edited Feb 26 '14 at 10:20

answered Feb 26 '14 at 07:37

Hamidreza

3,038
1
18
15

tnx Hamid Reza, I tried convert it for PHPMYADMIN and import it by a text file..but I got these error: #1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) – user3307827 Feb 26 '14 at 10:09
and the changed code: DELIMITER $$ CREATE FUNCTION dnmtr_topid(input_id INT UNSIGNED) RETURNS INT UNSIGNED BEGIN DECLARE in_id INT UNSIGNED; DECLARE v_pid INT UNSIGNED; SET in_id := input_id; WHILE in_id > 0 DO SET v_pid := in_id; SELECT parent_id into in_id FROM dnmtr_all_category WHERE id = in_id LIMIT 1; END WHILE; RETURN v_pid; END $$ – user3307827 Feb 26 '14 at 10:10
"dnmtr" is the prefix DB – user3307827 Feb 26 '14 at 10:10

score 1 · Answer 3 · answered Feb 26 '14 at 09:43

DROP FUNCTION IF EXISTS HATEST;
DELIMITER //
CREATE FUNCTION HATEST(input_id INT UNSIGNED)
  RETURNS INT UNSIGNED
BEGIN
  DECLARE in_id   INT UNSIGNED;
  DECLARE v_pid  INT UNSIGNED;

  SET in_id := input_id;

  WHILE in_id > 0 DO
    SET v_pid := in_id;
    SELECT parent_id into in_id FROM TABLE1 WHERE id = in_id LIMIT 1;
  END WHILE;

  RETURN v_pid;
END//
DELIMITER ;

Return parent ID in a nested table in sql

3 Answers3