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?

CodePudding user response：

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();