Left join does not work

Question

I have a problem on joining two tables.

I have two tables with fields [as shown below]:

Table 1 : prospectus - 
          idprospectus(int(2)), subjectcode(varchar(40)), units(int(2))

Table 2 : Student - 
          studentid(int(10)), subjectcode(varchar(40)), grade(varchar(10)), remarks(varchar(30))

What I want is to join these two tables even if the a value for subjectcode in the Student table doesn't exist (The prospectus table contains all the subjects or subjectcodes, while Student table only has the subjectcode once a student is enrolled to that subject and it also contains the grade of the subject the student is enrolled to.)

I already have tried executing the query below:

select p.subjectcode, s.grade, s.note 
from prospectus p 
left join student s on s.subjectcode=p.subjectcode 
where s.studentid='2011-10258' 
order by p.idprospectus

But the above query will only give me this:

--------------------------------
| subjectcode | grade |  Note  |
--------------------------------
| Eng         | 89    | Passed |
| Math        | 93    | Passed |
| Sci         | 90    | Passed |
--------------------------------

I already have tried using right join, inner, outer join but it will all give me the same output as shown above.

You can refer to my preferred output below:

--------------------------------
| subjectcode | grade |  Note  |
--------------------------------
| Eng         | 89    | Passed |
| Math        | 93    | Passed |
| Sci         | 90    | Passed |
| Soc         | NULL  | NULL   |
| Lit         | NULL  | NULL   |
--------------------------------

Could someone help me get rid of this, please ?

Answer 1

It's because you are using WHERE clause, try to use AND instead.

select p.subjectcode, s.grade, s.note 
from prospectus p 
left join student s on s.subjectcode=p.subjectcode 
AND s.studentid='2011-10258' 
order by p.idprospectus