How To Select All Child Rows

Question

I have a table with Id,Name and ParentId, like this

Id      Name      ParentId
----+----------+---------
 1      jijo        0
 2      arjun       1(Id)
 3      dijo        2(Id)

when I write a condition Id=3 ,I want to get all the child rows. I try many codes, but unlucky....

DELIMITER //
DROP PROCEDURE IF EXISTS `view_master_data_child_rows` //
CREATE  PROCEDURE `view_master_data_child_rows`(IN SearchID INT) 
  DECLARE ParentId INT DEFAULT SearchID;

  IF ParentId != 0 THEN
    SELECT `ParentId ` INTO ParentId FROM master_data WHERE `Id` = ParentId;
    CALL view_master_data_child_rows(ParentId);
  END IF;

END //
DELIMITER ;

Thanks.

Plz include what are you tried and sample data expected output too. — Sumesh TG, Oct 08 '18 at 09:34
It is a recursive hierarchical query. Based on your sample data, there are no child rows for `id = 3`. Please add expected output also to the question. — Madhur Bhaiya, Oct 08 '18 at 09:35
Go through this answer: https://stackoverflow.com/a/33737203/2469308 — Madhur Bhaiya, Oct 08 '18 at 09:37
@Madhur Bhaiya that code works well, when i put my child id i will get all the parrents. but I want the reverse result, ie when i put the parent id I want all the childs — JIJOMON K.A, Oct 08 '18 at 10:00
@JIJOMONK.A Can you prepare a SQL Fiddle ? Please share the link once done. I will try. — Madhur Bhaiya, Oct 08 '18 at 10:06

score 0 · Answer 1 · answered Oct 08 '18 at 09:39

0

Try Self Join

SELECT A.Id, A.Name,B.ID, B.Name
FROM master_data AS A
LEFT JOIN master_data AS B ON A.Id = B.ParentId
WHERE A.Id = 3;

answered Oct 08 '18 at 09:39

Vignesh Kumar A

27,863
13
63
115

it returns only one row – JIJOMON K.A Oct 08 '18 at 09:43

How To Select All Child Rows

1 Answers1