How to select specific course by selecting course id and concatenate users all selected course with in same row

Question

I am joining users table with selected_course table and course table with selected_course, my requirement is I want to get those users with course_id=2 and in same row select using concatenation of all courses which is selected by user.

user table

id   name
1     user_1

2     user_2

user_education_details

id      user_id  education_id  selected_course  

1        1          1               2

2        2          1               2

3        1          2               4

4        3          1               2


5        3          2               4

Actual Requirement:

user_id   required_course     user_all_courses    course_name


1             2                      2,4            a,b

2             2                      2               a

3             2                     2,4             a,b

Using codeigniter query format:

$this->db->select('users.*');
$this->db->from('users');
$this->db->join('selected_course sc','c.user_id=users.id');
$this->db->join('course c','c.id=sc.selected_course');
$this->db->where('sc.selected_course',$course_id=2);
$this->db->get()->result();

Answer 1

A HAVING clause is what you're looking for. To filter data based on a group use this clause.

SELECT 
    ued.user_id, 
    2 AS required_course,   -- passed course id
    GROUP_CONCAT(ued.selected_course) AS user_all_courses, 
    GROUP_CONCAT(c.name) course_name
FROM user_education_details ued
INNER JOIN courses c ON ued.selected_course = c.id
GROUP BY user_id
HAVING SUM(IF(selected_course = 2, 1,0)) > 0;

Output:

| user_id | required_course | user_all_courses | course_name | 
| 1       | 2               | 4,2              | b,a         | 
| 2       | 2               | 2                | a           | 
| 3       | 2               | 2,4              | a,b         | 

You can also order user_all_courses and course_name by using a ORDER BY clause inside GROUP_CONCAT. Here is one example.

GROUP_CONCAT(ued.selected_course ORDER BY selected_course ASC) AS user_all_courses, 

Now you can quickly convert this to your equivalent CodeIgniter query.

Check this working fiddle for the same.