To get all children and parent using id MySQL

Question

I have one table named group and i want to get all childrens and parents by using specific id:

Here is my table structure:

CREATE TABLE IF NOT EXISTS `group_tbl` (
  `group_id` int(11) NOT NULL AUTO_INCREMENT,
  `group_name` varchar(255) NOT NULL,
  `parent_id` int(11) NOT NULL,
  PRIMARY KEY (`group_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;


INSERT INTO `group_tbl` (`group_id`, `group_name`, `parent_id`) VALUES
(1, 'Test', 0),
(2, 'Test1', 1),
(3, 'Test3', 2),
(4, 'Test4', 3),
(5, 'Test5', 3),
(6, 'Test11', 4);
(7, 'Test12', 0),
(8, 'Test34', 8);

if i pass 3,outpur would be

(1, 'Test', 0),
(2, 'Test1', 1),
(3, 'Test3', 2),
(4, 'Test4', 3),
(5, 'Test5', 3)
(6, 'Test11', 4);

How can i get using single query or store procedure or any php function?

Why there will be such output, if you pass `3`??? Do you want all children and all parents??? — Legionar, Oct 20 '15 at 11:17
I don't think that this is possible using this structure of array. http://stackoverflow.com/questions/990529/mysql-how-to-find-all-ids-of-children-recursively — Kostas Mitsarakis, Oct 20 '15 at 11:19
@Legionar: Yeah i need all level children and parent. it is my requirement and why does it matter? — Bhumi Shah, Oct 20 '15 at 11:20
If you pass `3`, then children names will be `Test4`, `Test5` and `Test11` and parent name will be `Test1`. There is no sense of your example with `3`... — Legionar, Oct 20 '15 at 11:22
? So it will return all records??? Check your example - its the same as your sample data... — Legionar, Oct 20 '15 at 11:54
but if i add one more row with parent 0,it will not return all — Bhumi Shah, Oct 20 '15 at 12:05
No one has answer but all are ready to minus vote on stack overflow — Bhumi Shah, Oct 20 '15 at 13:35

Taher · Answer 1 · 2015-10-20T12:14:12.297

-1

SELECT * FROM `group_tbl` g1 JOIN `group_tbl` g2 ON g1.id=g2.parent_id WHERE g1.id = 3

edited Oct 20 '15 at 12:14

answered Oct 20 '15 at 12:08

Taher

11,902
2
28
44

not working as it is i need records only matched with id 3 where it is giving all records – Bhumi Shah Oct 20 '15 at 12:13

To get all children and parent using id MySQL

1 Answers1