How to join a table multiple times and retrieving one column value each time

Question

I have a Laravel 5.8 project and I need to join 3 tables for showing some results.

Basically, I have stored all the custom ids in a table called baseinfos.

For example here are two custom ids and I need to retrieve their bas_value:

So I tried this:

$records = DB::table('members')
            ->where('mys_olp_id',4)
            ->join('students', 'members.mbr_usr_id', '=', 'students.std_mbr_id')
            ->join('baseinfos', 'students.std_degree_id', '=', 'baseinfos.bas_id')
            ->join('baseinfos', 'members.mbr_gender_id', '=', 'baseinfos.bas_id')
            ->select('baseinfos.bas_value', 'baseinfos.bas_value')
            ->get()->toArray();

But this is wrong and shows me the error:

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique

So I need to say something like this:

// as student_degree
->join('baseinfos', 'students.std_degree_id', '=', 'baseinfos.bas_id') 

// as member_gender
->join('baseinfos', 'members.mbr_gender_id', '=', 'baseinfos.bas_id') 

And then:

->select('student_degree', 'member_gender')

How can I do this?

Answer 1

Try using like this with alias:

$records = DB::table('members ass mm')
            ->where('mm.mys_olp_id',4)
            ->join('students as ss', 'mm.mbr_usr_id', '=', 'ss.std_mbr_id')
            ->join('baseinfos as bif', 'ss.std_degree_id', '=', 'bif.bas_id')
            ->join('baseinfos as bifs', 'mm.mbr_gender_id', '=', 'bifs.bas_id')
            ->select('mm.*')
            ->get()->toArray();

You can also use leftJoin if it suits for you:

$records = DB::table('members ass mm')
            ->where('mm.mys_olp_id',4)
            ->leftJoin('students as ss', 'mm.mbr_usr_id', '=', 'ss.std_mbr_id')
            ->leftJoin('baseinfos as bif', 'ss.std_degree_id', '=', 'bif.bas_id')
            ->leftJoin('baseinfos as bifs', 'mm.mbr_gender_id', '=', 'bifs.bas_id')
            ->select('mm.*')
            ->get()->toArray();