+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: MySQL JOIN (/showthread.php?tid=48906)
MySQL JOIN - El Forum - 02-01-2012
[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!
MySQL JOIN - El Forum - 02-01-2012
[eluser]bgreene[/eluser]
select from the subjects table, left (or outer) join the grades table
MySQL JOIN - El Forum - 02-01-2012
[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
MySQL JOIN - El Forum - 02-01-2012
[eluser]ibnclaudius[/eluser]
When i removed:
Code:
$this->students_grades_table . '.user_id' => $user_id