0

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);
  • Please, tag your MySql version. – McNets Jan 30 '18 at 13:58
  • There are some good answer on SO here and here – McNets Jan 30 '18 at 14:01
  • I used mysql-5.6 – Atish Jan 31 '18 at 5:19
  • Hey McNets, the second link which is you are gave, it's return parent of child, I want to fetch child form parent id. – Atish Jan 31 '18 at 6:42
0

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 tag.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.