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.

See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Oct 10 '18 at 10:30
Edit your question to add expected output in tabular format (based on your given sample data) — Madhur Bhaiya, Oct 10 '18 at 10:41

score 1 · Accepted Answer · answered Oct 10 '18 at 10:50

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

score 0 · Answer 2 · answered Oct 10 '18 at 10:31

0

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`)
....

answered Oct 10 '18 at 10:31

sagi

40,026
6
59
84

thanks for the reply..my issue i always got same output if user id=3 or userid=2 – Ezra Oct 10 '18 at 10:49

mysql join query didnt get correct out put

2 Answers2