Getting child and grandchild users from mysql query

Asked Aug 09 '18 at 14:36

Active Apr 29 '21 at 13:45

Viewed 49 times

Actually I have a table called users and I have to pull out the child and grandchild users of a specific user like I am trying to get child and grandchild users of user with id 44 and I tried this query but it is giving me some extra users even they are not child or grand child of user 44:

SELECT * FROM `users` WHERE `parent_id`='44' OR `parent_id` IN (SELECT `id` FROM `users` WHERE `parent_id`='44' OR `parent_id` IN (SELECT `id` AS 'curret_id' FROM `users` WHERE `parent_id`='44' OR `parent_id` IN (SELECT `id` FROM `users` WHERE `parent_id`='curret_id')))

Here is CREATE TABLE Syntax for my table if it helps:

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `join_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fname` varchar(250) NOT NULL,
  `lname` varchar(250) NOT NULL,
  `email` varchar(250) NOT NULL,
  `phone` varchar(250) NOT NULL,
  `dob` date NOT NULL,
  `user_type` varchar(250) NOT NULL,
  `parent_id` int(11) NOT NULL,
  `status` varchar(250) NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

edited Apr 29 '21 at 13:45

Dharman

30,962
25
85
135

asked Aug 09 '18 at 14:36

GamesPlay

give your CREATE TABLE syntax, some sample data and expected output – Madhur Bhaiya Aug 09 '18 at 14:39
It is there now – GamesPlay Aug 09 '18 at 14:43
what is 'curret_id' in your original SQL – Madhur Bhaiya Aug 09 '18 at 14:45
I am getting child id as current_id look at the query like : SELECT `id` AS 'curret_id' FROM `users` WHERE `parent_id`='44' – GamesPlay Aug 09 '18 at 14:47
look at recursive queries https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query – Madhur Bhaiya Aug 09 '18 at 14:47
Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) – Madhur Bhaiya Aug 09 '18 at 14:48

Getting child and grandchild users from mysql query

0 Answers0