certainly looks a lot simpler than what you had before
Originally Posted by aamonkey
on a minor note, i try to write my FROM clause in a logical sequence, one that mirrors how i hope/assume the optimizer will approach it -- starting with the essential or most restrictive table, and joining from there (so as to minimize the number of rows that it retrieves only to throw away)
since your whole query is about glasses, this is how i would write it...
note also that DISTINCT is ~not~ a function, and whatever comes after it does not need to be placed in parentheses
SELECT COUNT( DISTINCT V.clientId )
FROM SystemSetup_Items I
JOIN ClientManagement_Client_Voucher_Items VI
ON VI.itemId = I.id
JOIN ClientManagement_Client_Vouchers V
ON V.id = VI.voucherId
AND V.dateVoided IS NULL
AND V.dateCreated >= '2010-01-01'
AND V.dateCreated <= '2010-12-31'
JOIN ClientManagement_Clients C
ON C.id = V.clientId
AND DATE_FORMAT(V.dateCreated, '%Y') -
DATE_FORMAT(C.dob, '%Y') -
(DATE_FORMAT(V.dateCreated, '00-%m-%d') <
DATE_FORMAT(C.dob, '00-%m-%d')) >= 60
WHERE I.description = 'Glasses'