Join 3 tables in MySQL in single query

Here in this tutorial, we are making an SQL query that will Join 3 tables in a single query.

First, I will tell you why query join is important, it will increase the page loading speed. currently, you are running 1 query instead of three. if we are in a loop there we can save hundreds of query by using this SQL join feature.

Let’s look into a scenario.  We have a student table. and we need to get the class teacher data and course data. so what we do ..

Please check the table interface below.

Here you can see 3 tables so let’s look at how we are joining these 3 tables.

SELECT * FROM students
LEFT JOIN teachers
ON teachers.ID= students.teacher
LEFT JOIN courses
ON courses.id= teachers.id

Yes, this will get all the data from these 3 tables. but there is a problem .. we have the same field names in different tables. so what we do with that

Don’t worry I will help you. we will manually assign the field names

Here we go.

SELECT s.name as studentname, s.teacher, s.course,
t.name as teachername ,
c.name as coursename 
FROM students s 
LEFT JOIN teachers t ON t.id = s.teacher 
LEFT JOIN course c ON c.id = s.course

So here we are renaming the teacher name , student name and course name into unique name fields. so the results wont have multiple same-name fields.

Hope this helps .