Question 21836 in Mysql Question posted in
The official MySQL documentation can be found here.

Anyone help me write a MySQL query

Data from table users

enter image description here

As you can see there are id and added_by_id columns, I want to fetch all the users added by a specific id but if the id in that result has also added someone then I should get that also.

Example:- if I want to find all the IDs added by 1 then I should get 2,3,4,5,6,7. Now if you are thinking why 4, that’s, because 4 was added by 2, and 2, was added by 1.

And if I want to find all the IDs added by 2 then I should get 4 and other IDs added by 4 and other IDs added by Other IDs….I hope you got my point.

They can only see below not above in the hierarchy.

Can anyone help me write this complex query?

Want to get kind of recurring details…I don’t know if I explained it correctly.

Tags:

2

Answers


  1. 0

    I think you can add another column, called first_level_id, you put the highest parent user_id that you can get when inserting new rows, and probably refresh all the existing rows for the value stored in first_level_id, and after that, you can get what you want with

    select id from xxx where first_level_id = 1;
    Login or Signup to reply.
  2. 0

    Hey here is query that I’ve tried. I Hop this will help you.

    SELECT id FROM(
WITH RECURSIVE 
cte AS ( SELECT *
         FROM tb_user
         WHERE id = 1
       UNION ALL
         SELECT tb_user.*
         FROM cte
         JOIN TBL1 ON  tb_user.id =cte.added_by_id )
SELECT id , added_by_id 
FROM cte ) as T WHERE T.added_by_id IS NOT NULL AND parent!=1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search