How to return the 'Parent ID (Main)' in this Mysql query?

Asked

Viewed 113 times

1

I’m trying to return the parent ID at the highest level in a query, this is the information:

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

Fiddle

mysql

  • You want to return also the father ID "root", that’s it?

  • That’s right @Rodrigorigotti

  • It wouldn’t be the same FATHER_ID ?

  • How many levels?

  • FATHER_ID is what I need, I only have the father on the first level. I need the general parent. @Antonyalkmim

  • @Leandroamorim can have N levels :(

  • And you need it in SQL itself, or will use some client language?

  • Need in SQL friend @Bacco

  • @Bacco I have a View of the information I need from Redmine and consume it by WSDL and my application only sends the parameters and receives the list. In this case I need to filter all the tasks of using only the ID of the main project, so I wanted to add this field in my View.

  • @Bacco Tranquiloo! With the trial I believe that it would not work, already the function I think will work! I will make a test

  • 1

    @Bacco worked perfectly! Thank you very much!

Show 6 more comments

1 answer

4


A possible solution is a function, but if it is something that will be widely used, it compensates or add a field in the table to the category "master", and process the tree only in modifications and inserts, or else use a methodology with nodes left and right, so you can do the tree search.

It follows a recursive function that locates the upper level and may be adapted to the real case:

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

See working on SQL Fiddle.

And with a JOIN showing the main section: SQL Fiddle.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.