CODEWITHSUNDEEP
sqlrecursive-query
Osakr
Osakr

Reputation: 1066

Doing a recursive query in SQL to get data in cascade

Think in an scenario where you have a table of users. The users have a role assigned and a referer field which is the ID of other user with a higher role. Let's assume that we have 5 roles having the fifth the higher one. The referer id is always the ID of the user who has added this user and it only can be in one role higher

I want to get all the users who are below the role 5 ( starting with the role 4 ) but they must be refered by the user or a user who has been refered by my user and so on. Example:

User1: [ role: 5, referer: 0 (because is the first user), id: 1]
User2: [ role: 4, referer: 1, id:2]
User3: [ role: 4, referer: 44, id: 3]
User4: [ role: 3, referer: 2, id: 4]
User6: [ role: 2, referer: 4, id: 5]
User7: [ role: 1, referer: 5, id: 6]
User8: [ role: 1, referer: 44, id: 7]

So I want to query using the User1 data and the response should have the User2, User4, User6, User7.

Any help would be appreciated.

Upvotes: 0

Views: 101

Answers (1)

Ajay Gupta
Ajay Gupta

Reputation: 1855

Using recursive CTE

with cte as
(Select * from tbl where id=1
union all 
Select u.id, u.role,u.referer from tbl u
join cte c on c.id = u.referer
)
Select * from cte where role <(Select role from tbl where id=1)

Upvotes: 2

Related Questions