The following query is doubling the count of "cases" in the patients table. The "doc" in the members table has only one case but the query is reporting 2!


Thanks

Oldnickj

SELECT members.mfirst , members.mmiddle , members.mlast , members.id , members.member_id , label.descr_lbl , EXTRACT(YEAR
FROM patients.cdate) AS Year , EXTRACT(MONTH FROM patients.cdate) AS yyyymm , COUNT(patients.doc_id)
FROM members
INNER
JOIN label ON label.value_lbl = members.demo5
INNER JOIN patients ON patients.doc_id = members.id AND members.mlast ='jewett'
GROUP BY members.id , yyyymm
ORDER BY mlast, patients.cdate

I should add that we have about 35k records which is slowing things a bit.

Nick

Then the next question is what can I do to optimize the tables or the query?

Oldnickj

The solution I received was that I had two "labels" with the same value. Thank God for forums!