I'm trying to return the parent ID at the highest level in a query, here's 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
A possible solution is a function, but if it is a thing that will be used a lot, it compensates or adds a field in the table to the category "master", and processes the tree only in modifications and insertions, or use a methodology with nodes left and right, in order to search the tree.
Here is a recursive function that will find the top level and maybe it can 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 in SQL Fiddle .
And with a JOIN showing the main section: SQL Fiddle .
p>