How to return 'Father ID' in this query?

Question

I'm trying to return the father ID of my table (MySQL), this is the return:

ID  NOME         PARENT_ID
1   Project      (null)
2   Sub          1
3   Sub-Sub      2
4   Sub-Sub-Sub  3

I need this:

ID  NOME         PARENT_ID FATHER_ID
1   Project      (null)    (null)
2   Sub          1         1 
3   Sub-Sub      2         1
4   Sub-Sub-Sub  3         1

Here is the fiddle

This is basically a duplicate of http://stackoverflow.com/questions/10646833/using-mysql-query-to-traverse-rows-to-make-a-recursive-tree. TLDR: you can't easily do it. — DanSingerman, Dec 05 '14 at 16:22

score 0 · Accepted Answer · edited Apr 13 '17 at 12:59

0

I had to use a function like this (link SO PT-BR):

CREATE FUNCTION getRoot(idSearch INT)
RETURNS INT DETERMINISTIC
BEGIN
  SET @idParent = idSearch;
  WHILE (@idParent != 0) DO
    SET @idFound = @idParent;
    SET @idParent = (SELECT id_parent FROM arvore WHERE id = @idParent);
  END WHILE;
  RETURN @idFound;
END

SQL Fiddle.

SQL Fiddle2.

edited Apr 13 '17 at 12:59

Community

1
1

answered Dec 09 '14 at 18:50

Alessandro Gomes

521
1
5
14

How to return 'Father ID' in this query?

1 Answers1