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
– Sankar Smith, Commented Nov 18, 2016 at 8:36

Community · Accepted Answer · 2017-05-23 12:13:46Z

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, 2017 at 12:13

CommunityBot

11 silver badge

answered Nov 18, 2016 at 8:42

Matey

1,2106 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sankar Smith · Accepted Answer · 2016-11-18 09:26:11Z

0

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

answered Nov 18, 2016 at 9:26

Sankar Smith

3562 gold badges5 silver badges15 bronze badges

Collectives™ on Stack Overflow

Laravel Left Join Query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related