Recursive query problem

Question

i have a table which contains the following fields

•Supervisorid •Empid This is just like a referral program. A guy can refer 3 guys under him i.e, 3 is referring three guys namely 4 5 8 similarly 4 is referring 9 10 and 11 likewise 8 is referring 12, 13 it goes like this..

I want a query to get all EmpId under Supervisor 3

Answer 1

In order to do that you'd need to join the table with itself many times, which may not be good. Also you need to know the depth of referrals in advance to construct such a query.

If your table doesn't have too many rows (e.g >1000) the best solution is to simply do a select * and calculate the tree in the application layer (e.g in php).