What I am doing?
I am displaying the user according to there studied. I want to fetch the last course studied by each user.
Problem?
I am not getting the last course name according to last studied course. I am getting the first course name.(java) but i want (oracle) as last studied course by user.
User Table
user_id | Name
====================
1 | Zishan
2 | Ellen
Course Table
course_id | course_name | user_id | course_year
==================================================
1 | java | 1 | 2015
2 | C++ | 1 | 2017
3 | oracle | 1 | 2016
4 | dot net | 2 | 2016
Result Table
Name | last_course_name | last_course_year
============================================
Zishan | java | 2017
Ellen | dot net | 2016
Expected Result
Name | last_course_name | last_course_year
============================================
Zishan | C++ | 2017
Ellen | dot net | 2016
Query
SELECT `u`.`name`, MAX(`c`.`course_year`) as last_course_year , `c`.`course_name` as last_course_name
FROM `user` as `u`
LEFT OUTER JOIN `course` as `c` ON `u`.`id` = `c`.`user_id`
GROUP BY `u`.`id`
Active Record Query:
$this->db->select('u.name','c.course_name as last_course_name');
$this->db->select_max('c.course_year as last_course_year');
$this->db->from('user as u');
$this->db->join('course as c', 'u.id = c.user_id', 'left');
$this->db->join('course as c1', 'c.user_id = c1.user_id', 'left outer');
$this->db->where('c1.user_id IS NULL', null, false);
$this->db->group_by('u.id');
$user_couse_data_query = $this->db->get();