Issue with Group by mysql

Question

I have a query

return $this->db->select ("SELECT `c_id`,`c_name`,(select count(`a_id`) nam from Admin_course_groups where fk_c_id = `c_id` and fk_g_id = $id)adm from admin_courses");

and output:

Array
(
    [0] => Array
        (
            [c_id] => 1
            [c_name] => Java - Basic Operators
            [adm] => 1
        )

    [1] => Array
        (
            [c_id] => 3
            [c_name] => JAVA Questions and Answers
            [adm] => 0
        )

    [2] => Array
        (
            [c_id] => 4
            [c_name] => Java - Collections Framework
            [adm] => 0
        )

)

So i want to get start date and end date from this query.so i tried

 return $this->db->select ("SELECT `c_id`,`c_name`,count(`a_id`) nam,start_date,end_date from admin_courses join Admin_course_groups on  fk_c_id = `c_id` where fk_g_id = $id group by `a_id`");

Which return only one result

Array
(
    [0] => Array
        (


[c_id] => 1
            [c_name] => Java - Basic Operators
            [nam] => 1
            [start_date] => 2018-01-01
            [end_date] => 2018-09-25
        )

)

Expected output

 Array
    (
        [0] => Array
            (
                [c_id] => 1
                [c_name] => Java - Basic Operators
                [nam] => 1
                [start_date] => 2018-01-01
                [end_date] => 2018-09-25
            )

        [1] => Array
            (
                [c_id] => 3
                [c_name] => JAVA Questions and Answers
                [adm] => 0
                [start_date] =>
                [end_date] =>
            )

        [2] => Array
            (
                [c_id] => 4
                [c_name] => Java - Collections Framework
                [adm] => 0
                [start_date] => 
                [end_date] => 
            )

    )

Table admin_courses

c_id      |     C_name           |         C_desc | C_status
1           Java - Basic Operators        test        1
2          JAVA Questions and Answers     test2       1
3          Java - Collections Framework   test3        1

Table Admin_course_groups

a_id | fk_c_id |fk_g_id|start_date|end_date
 1      1          2      2018-01-01 2018-09-25

Any help would be Appreciated.

Answer 1

You have a couple of issues with your query that lead to it not returning all the results you want. Firstly, you are using a JOIN and there is only one matching row in admin_course_groups so that restricts you to one row. You should use a LEFT JOIN instead. Secondly, you are grouping by a_id when you should group by c_id. Finally after changing to a LEFT JOIN you will need to modify the WHERE clause to allow fk_g_id to be NULL. This query should give the results you want:

SELECT `c_id`, `c_name`, COUNT(`a_id`) AS nam, MIN(start_date), MIN(end_date)
FROM admin_courses c
LEFT JOIN admin_course_groups g
ON g.fk_c_id = c.`c_id` 
WHERE fk_g_id = 2 OR fk_g_id IS NULL
GROUP BY `c_id`

Output:

c_id    c_name                          nam     start_date  end_date
1       Java - Basic Operators          1       2018-01-01  2018-09-25
2       JAVA Questions and Answers      0       (null)      (null)
3       Java - Collections Framework    0       (null)      (null)

SQLFiddle Demo