fetch the recursive data in sql query

Question

I have the table user which has user_name and manager name.I want to get the user name for who have manager ='test'.I want to get the user who are having ramya as manager and reportee's reportee

users table 
SNO user_name   manager
1    vani        Ramya
2    ramya       geetha
3    priya       ramya
4    mani        ramya
5    latha       vani
6.   hema        Anitha 

I want to get the name ramya and ramya's reportee vani,priya and mani and get latha who is having vani as manager who is having ramya as manager. I want to get ramya,vani,priya,mani and latha

Answer 1

You can use below query :

select a.user_name from 
users a left join users b
on a.manager=b.user_name
where a.user_name='Ramya' or a.manager='ramya' or b.manager='Ramya'

Output:

| user_name |
|-----------|
|     latha |
|      vani |
|     priya |
|      mani |
|     ramya |

SQL Fiddle : http://sqlfiddle.com/#!9/f57b06/10

P.S : I've never used Laravel before but after reading some docs I think the query could be written this way in there

$users = DB::table('users as a')
            ->select('a.user_name')
            ->leftJoin('users as b', 'a.manager', '=', 'b.user_name')
            ->where('a.user_name','Ramya')
            ->orwhere('a.manager','Ramya')
            ->orwhere('b.manager','Ramya')
            ->get();