Procedure for tree view

Question

I trying to fetch data form database which is have 3 column unique_id, parent_id & node_name, but this query (Given in below) is return only two rows.

Condition: every parent has two child.

DROP PROCEDURE `treeTbl`;
CREATE DEFINER=`bizsol`@`localhost` PROCEDURE `treeTbl`(IN `childId` INT(255)) DETERMINISTIC NO SQL SQL SECURITY DEFINER BEGIN
 DECLARE tempId INT DEFAULT 0;
 SELECT *, unique_id as id FROM t_users WHERE 1 AND parent_id = childId;
 IF(tempId>0)
  THEN
    SET tempId = id;
    CALL treeTbl(tempId);
 END IF;
END

And call form here,

CALL treeTbl(3);

Answer 1

3 column unique_id, parent_id & node_name, but this query (Given in below) is return only two rows.

MySQL doesn't have Tail Call Optimization. Without TCO, each call will take more memory, and get a different stack. So implementing it in this fashion is at the very best insecure because you can't ensure through a constraint that the query won't result in SQL dying. Case and point, retrying the query in such a case will result in another dead backend. So now you gotta do something other than retry. Any answer that uses this method is pretty misguided.

So what do you do? Upgrade to MySQL 8 beta. That has Recursive CTEs. This is called a hierarchical query, or even better -- migrate to PostgreSQL which is a superior database that has had this feature for like a decade.

For more information, see the hierarchy tag.