Problems joining three tables and grouping by and having count

[raymond_feliciano]

I have created a search form which joins three tables on a where clause which could have nth number of values. Here is my query

SELECT ci_patient_info .* 
FROM ci_patient_info
LEFT JOIN ci_study_history ON ci_patient_info.account_num = ci_study_history.account_num
LEFT JOIN ci_prescription_history ON ci_patient_info.account_num = ci_prescription_history.account_num
WHERE ci_study_history.study
IN (
'study 1','study 2'
)
AND ci_prescription_history.prescription
IN (
'script 1','script 2'
)
GROUP BY ci_patient_info.account_num
HAVING count(*) = 4

At the moment this will work and I will see the two rows I expect to see. If I add another script to the second IN clause I should see the same two rows but instead I get an empty result set. Here is the query for that.

SELECT ci_patient_info .* 
FROM ci_patient_info
LEFT JOIN ci_study_history ON ci_patient_info.account_num = ci_study_history.account_num
LEFT JOIN ci_prescription_history ON ci_patient_info.account_num = ci_prescription_history.account_num
WHERE ci_study_history.study
IN (
'study 1','study 2'
)
AND ci_prescription_history.prescription
IN (
'script 1','script 2','script 3'
)
HAVING count(*)= 5 

Am I writing these queries wrong because I cant seem to get the results if I added anymore values to any of the IN clauses.

 

I even tried this with no luck.

SELECT ci_patient_info .* 
FROM ci_patient_info
LEFT JOIN ci_study_history ON ci_patient_info.account_num = ci_study_history.account_num
LEFT JOIN ci_prescription_history ON ci_patient_info.account_num = ci_prescription_history.account_num
WHERE ci_study_history.study
IN (
'study 1','study 2'
)
AND ci_prescription_history.prescription
IN (
'script 1','script 2','script 3'
)
HAVING count(ci_study_history.study)= 2 
AND count(ci_prescription_history.prescription) = 3 

I am fairly new to MySQL so  any suggestions are appreciated

[requinix]

Multiplication, not addition. 2 studies * 3 prescriptions = 6 rows.

[raymond_feliciano]

if your telling me the query should look like this

SELECT ci_patient_info .* 
FROM ci_patient_info
LEFT JOIN ci_study_history ON ci_patient_info.account_num = ci_study_history.account_num
LEFT JOIN ci_prescription_history ON ci_patient_info.account_num = ci_prescription_history.account_num
WHERE ci_study_history.study
IN (
'study 1','study 2'
)
AND ci_prescription_history.prescription
IN (
'script 1','script 2','script 3'
)
HAVING count(*)=6 

This still returns an empty result set

[raymond_feliciano]

sorry i didnt realize ti left the group by out. It work like a charm thanks dude really I have been working on this for 6 hours.