Anyone help me write a MySQL query-CodePudding

Data from table users

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.

CodePudding user response：

Hey Here is Query what I've tried. I Hop this will help you.

SELECT id FROM(
WITH RECURSIVE 
cte AS ( SELECT *
         FROM TBL1 
         WHERE id = 1
       UNION ALL
         SELECT TBL1.*
         FROM cte
         JOIN TBL1 ON  TBL1.id =cte.added_by_id )
SELECT parent, addedby
FROM cte ) as T WHERE T.added_by_id IS NOT NULL

CodePudding user response：

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;