mysql join query didnt get correct out put

Question

I have 3 tables

• admin_courses
• Admin_course_groups
• Group_permision

admin_courses

c_id | c_name  | c_status
  1 |   test1  | 1
  2 |test2     |1
  3 |test3     |1
 4   test4      1

Admin_course_groups

a_id | fk_c_id  |fk_g_id |start_date |end_date
  1 |   1         | 1     | 2018-10-10 |2018-10-20
  2 |   5         |1      | 2018-10-10  | 2018-10-20
  3 |   4         |3      |2018-10-10   |2018-10-20

Group_permision
 gp_id|fk_g_id|user_id
1           1    2
2           3    2
2           1    3

Here total four courses added i want to know how many course assign to each user, i have query

SELECT c_id
     , c_name
     , COUNT(a_id) AS nam
     , MIN(start_date) as start_date 
     , MIN(end_date) as end_date 
  FROM admin_courses c 
  LEFT 
  JOIN Admin_course_groups g 
    ON g.fk_c_id = c.c_id 
  left 
  join Group_permision h 
    on g. fk_g_id=h.fk_g_id 
   and users_id=3 
 where c.c_status=1 
 GROUP 
    BY c_id

So here it will return all course, if course is assigned for user COUNT(a_id) will 1 else it 0. Now my issue is that if users_id is 3 user assigned only 1 course but i got 3 instead of 1. Please help me. any help would be appreciated.

Answer 1

• You are counting wrong field. To count number of Users in a Course, you need to count user_id; Also, use Count(Distinct ...) to avoid counting a same user multiple times.
• Also, note that I have added c.c_name to Group By clause, to be compatible with only_full_group_by mode. Do Read: SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by
• You dont need to join on user_id, to count total number of users. It restricts your data.

Try:

SELECT c.c_id
     , c.c_name
     , COUNT(DISTINCT h.user_id) AS nam
     , MIN(g.start_date) AS start_date 
     , MIN(g.end_date) AS end_date 
  FROM admin_courses AS c 
  LEFT 
  JOIN Admin_course_groups AS g 
    ON g.fk_c_id = c.c_id 
  LEFT 
  JOIN Group_permision AS h 
    on g.fk_g_id = h.fk_g_id 
 WHERE c.c_status=1 
 GROUP 
    BY c.c_id, 
       c.c_name 

Answer 2

Your question is missing some crucial information, but if I understood the problem correctly, I would simply try to add Distinct to the count :

SELECT `c_id`, `c_name`, COUNT(distinct `a_id`)
....