Error on Left Join three tables in mysql

Question

I set all the day reading Questions on stackoverflow of how to jouin 3 table in one SQL query , I try it many times but it did not work !!

my case are like this , I want to understand how it is work and how should I do it.

my SQL query is the following :

SELECT students.id,
       students.name,
       students.age,
       students.subjects,
       students.teacher from students
LEFT JOIN subjects ON students.subjects = subjects.subject_id
LEFT JOIN teachers ON students.teacher = teachers.teacher_id

What is the wrong in my case ?

you have a typo in there - it should be `from` not `form`. Also it would help if you could show your table schema — Professor Abronsius, Jul 20 '16 at 10:54
What about `LEFT JOIN teachers ON student.teacher = teachers.teacher_id_column_name` ? — Alok Patel, Jul 20 '16 at 10:57
Is your table `student` or `students` ? `SELECT students.id, students.name, students.age, students.subjects, students.teacher from students...` — ojovirtual, Jul 20 '16 at 10:57
Another typo: `student` should be `students`. Also, in your second `JOIN`, it should be something like this: `students.teacher` = `teachers`.`teacher_id`. — Marcello Mönkemeyer, Jul 20 '16 at 10:57
How is the data stored in `students.subjects`? Is it just a single id, like `subjects.subject_id`? — Daan Wilmer, Jul 20 '16 at 11:00

score 1 · Accepted Answer · answered Jul 20 '16 at 10:58

1

You are missing a condition in the second LEFT JOIN:

SELECT s.id, s.name, s.age, s.subjects, s.teacher 
FROM students s LEFT JOIN
     subjects su
     ON s.subjects = su.subject_id LEFT JOIN
     teachers t
     ON s.teacher = t.teacher;

You should note, though, that the query is only fetching columns from students, so you don't need the joins (at this point, I'm guessing the query you want is more complicated).

answered Jul 20 '16 at 10:58

Gordon Linoff

1,242,037
58
646
786

Maybe it's just me, but putting `LEFT JOIN` on the same line as `FROM` or `ON` looks totally weird... anyway, correct answer. – Marcello Mönkemeyer Jul 20 '16 at 11:07
where did the the "s" in students s came from ? – Evemtor Jul 20 '16 at 11:09
It's an alias. You can say `FROM students AS s` and, with that, refer to it with just `s`. Makes it shorter and a little clearer. The `AS` - as it's the case here - is optional. – Marcello Mönkemeyer Jul 20 '16 at 11:14

score -1 · Answer 2 · answered Jul 20 '16 at 10:59

-1

Try this bro

select stud.id,stud.name,stud.age,stud.subjects,stud.teacher
from  students stud, subjects sub ,teachers tea 
where stud.subjects = sub.subject_id and stud.teacher = tea.teacher_id

answered Jul 20 '16 at 10:59

Goutham

52
1
1
9

This works differently: a `LEFT JOIN` also selects students that do not have a teacher. – Daan Wilmer Jul 20 '16 at 11:02
(Side note / pet peeve: why do you abbreviate the table names when it does not significantly reduce the length? It only makes it harder to understand). – Daan Wilmer Jul 20 '16 at 11:04
@Daan Wilmer why you downvote my suggestion..Anything wrong in that – Goutham Jul 20 '16 at 11:04
You all guys having too much of reputations.so that you won't bother about new learners – Goutham Jul 20 '16 at 11:06
The answer is not clear, how ever I am not the one who downvot your answer, thanks any way – Evemtor Jul 20 '16 at 11:07
@Evemtor Thank you..iam just suggesting ..thats all – Goutham Jul 20 '16 at 11:07
@Goutham I downvoted because it is a wrong answer, in that the behaviour is slightly different from the desired behaviour: the desired behaviour seems to be that of a `LEFT JOIN`, you're describing an (implicit) `INNER JOIN`. See here for details: http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins#38578 Furthermore, you don't explain _why_ this should work. If you give an explanation, people can understand what went wrong, and improve their own coding. I'd be happy to remove the downvote if the answer were correct, and upvote for a good explanation. – Daan Wilmer Jul 21 '16 at 08:52
@DaanWilmer yes you are right.Thank you for your valuable suggestion and a deep tutorial about JOINS. – Goutham Jul 21 '16 at 09:17

Error on Left Join three tables in mysql

2 Answers2