I want to join 3 tables named members
, students
and baseinfos
.
And baseinfos holds some id
as bas_id
and name of it is stored in bas_value
:
And this is my code:
$records = DB::table('members') ->where('mys_olp_id',4) ->join('students', 'members.mbr_usr_id', '=', 'students.std_mbr_id') ->join('baseinfos as gender', 'members.mbr_gender_id', '=', 'gender.bas_id as gvalue') ->join('baseinfos as degree', 'students.std_degree_id', '=', 'degree.bas_id as dvalue') ->select('gender.gvalue', 'degree.dvalue') ->get()->toArray();
But this is wrong and shows me this error:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as `gvalue` inner join `baseinfos` as `degree`
Note that I can not say this:
$records = DB::table('members') ->where('my_students.mys_olp_id',4) ->join('students', 'members.mbr_usr_id', '=', 'students.std_mbr_id') ->join('baseinfos as degree', 'students.std_degree_id', '=', 'degree.bas_id') ->join('baseinfos as gender', 'members.mbr_gender_id', '=', 'gender.bas_id') ->select('gender.bas_value', 'degree.bas_value') ->get()->toArray();
Because degree.bas_value
will overwrites gender.bas_value
!
So how can I join these 3 tables properly?
Advertisement
Answer
Your aliase inside join does not formated correctly. I think you can achieve this in this way-
$records = DB::table('members') ->join('students', 'students.std_mbr_id', '=', 'members.mbr_usr_id') ->join('baseinfos as gender','gender.bas_id', '=', 'members.mbr_gender_id') ->join('baseinfos as degree','degree.bas_id' , '=', 'students.std_degree_id') ->where('mys_olp_id',4) ->select('gender.bas_id as gvalue', 'degree.bas_id as dvalue') ->get();