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?
CodePudding user response:
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();