Monday 3 September 2018

The mysql account does not work with the join clause and where

If I am using this query:

SELECT clinic.id
FROM clinic
JOIN dentist ON dentist.id = clinic.dentist_id
JOIN user ON user.id = dentist.user_id
WHERE user.status = 'Active'
AND clinic.status = 'Approved'
AND user.role_id = 2
GROUP BY clinic.id;

So it gives me all rows like
32
35
36
42
44
47
50

Bug If I am going to get total count of results so I apply query :
SELECT count(clinic.id) AS cnt
FROM clinic
JOIN dentist ON dentist.id = clinic.dentist_id
JOIN user ON user.id = dentist.user_id
WHERE user.status = 'Active'
AND clinic.status = 'Approved'
AND user.role_id = 2
GROUP BY clinic.id;

I just applied count() to id, and it returns
1
1
1
1
1
1
1

It does not returns 7, I want result as 7. Can anybody guide please.

DO NOT USE GROUP BY or try this
SELECT clinicID,SUM(case when clinic !=null then 1 ELSE NULL END).id AS cnt
FROM clinic
JOIN dentist ON dentist.id = clinic.dentist_id
JOIN user ON user.id = dentist.user_id
WHERE user.status = 'Active'
AND clinic.status = 'Approved'
AND user.role_id = 2
GROUP BY clinic.id;

0 comments:

Post a Comment