MySQL JOIN

[eluser]ibnclaudius[/eluser]
This query:

Code:

$query = $this->db->select($this->students_grades_table . '.grade AS user_grade, ' . $this->subjects_table . '.name AS subject_name')
    ->from($this->students_grades_table)
    ->join($this->subjects_table, $this->subjects_table . '.id = ' . $this->students_grades_table . '.subject_id', 'INNER')
    ->join($this->classes_table, $this->classes_table . '.id = ' . $this->subjects_table . '.class_id', 'INNER')
    ->where(array($this->classes_table . '.school_id' => $school_id, $this->students_grades_table . '.user_id' => $user_id))
    ->get();

return, for example, this:

user_grade | subject_name
-------------------------
5 | Math

How can I return the subject_name even if there's no grade associated?

For example:

user_grade | subject_name
-------------------------
5 | Math
- | English
- | History

Thanks, all!

[eluser]bgreene[/eluser]
select from the subjects table, left (or outer) join the grades table

[eluser]ibnclaudius[/eluser]
I tried this:

Code:

$query = $this->db->select($this->students_grades_table . '.grade AS user_grade, ' . $this->subjects_table . '.name AS subject_name')
    ->from($this->subjects_table)
    ->join($this->students_grades_table, $this->students_grades_table . '.subject_id = ' . $this->subjects_table . '.id', 'LEFT')
    ->join($this->classes_table, $this->classes_table . '.id = ' . $this->subjects_table . '.class_id', 'INNER')
    ->where(array($this->classes_table . '.school_id' => $school_id, $this->students_grades_table . '.user_id' => $user_id))
    ->get();

But still returning this:

user_grade | subject_name
————————————-
5 | Math

[eluser]ibnclaudius[/eluser]
When i removed:

Code:

$this->students_grades_table . '.user_id' => $user_id

from the query, it worked, but it's returning the grades form all users. I also need to select it by the user... How can I do it?