Laravel Left Join Query

Question

I am using laravel 5.3 and I have some left join query with error in laravel query method. This is my normal query

 SELECT bran.branchName,sch.schoolName From m_schoolbranch bran
LEFT JOIN m_students stu ON stu.schoolNo=bran.schoolNo AND stu.branchNo=bran.branchNo  
LEFT JOIN m_school sch ON sch.schoolNo=stu.schoolNo where stu.userNo='0000000001';

And this is my new laravel Query

DB::table('m_schoolbranch')
            ->join('m_students', 'm_schoolbranch.schoolNo', '=', 'm_students.schoolNo')
            ->join('m_students', 'm_schoolbranch.branchNo', '=', 'm_students.branchNo')
            ->join('m_school', 'm_schoolbranch.schoolNo', '=', 'm_school.schoolNo')
            ->select('m_school.schoolName', 'm_schoolbranch.branchName')
            ->where('m_students.userNo',$userNo)
            ->get();

In these query I need to match two column in table m_students so I put like this

->join('m_students', 'm_schoolbranch.branchNo', '=', 'm_students.branchNo')

But i show error...

QueryException in Connection.php line 729: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'm_students' (SQL: select `m_school`.`schoolName`, `m_schoolbranch`.`branchName` from `m_schoolbranch` inner join `m_students` on `m_schoolbranch`.`schoolNo` = `m_students`.`schoolNo` inner join `m_students` on `m_schoolbranch`.`branchNo` = `m_students`.`branchNo` inner join `m_school` on `m_schoolbranch`.`schoolNo` = `m_school`.`schoolNo` where `m_students`.`userNo` = 0000000001) — Sankar Smith, Nov 18 '16 at 08:36

score 0 · Answer 1 · edited May 23 '17 at 12:13

0

Tables in the query need to have unique names, otherwise the DB has no way of knowing which m_schoolbranch should be used when evaluating m_schoolbranch.schoolNo.

You could use unique table aliases in your join statements but I recommend using multiple conditions on the join. Just like you use in your original SQL query. See here: https://stackoverflow.com/a/20732468/4437888

edited May 23 '17 at 12:13

Community

1
1

answered Nov 18 '16 at 08:42

Matey

1,190
5
7

score 0 · Answer 2 · answered Nov 18 '16 at 09:26

DB::table('m_schoolbranch')
            ->join('m_students', function($join)
                {
                    $join->on('m_schoolbranch.schoolNo', '=', 'm_students.schoolNo');
                    $join->on('m_schoolbranch.branchNo', '=', 'm_students.branchNo');
                })
            ->join('m_school', 'm_schoolbranch.schoolNo', '=', 'm_school.schoolNo')
            ->select('m_school.schoolName', 'm_schoolbranch.branchName')
            ->where('m_students.userNo',$userNo)
            ->get();

Laravel Left Join Query

2 Answers2