I have problems with the following query...I want to get the maxdate from the group (ref_num) and then check it against the date_sub funciton...
SELECT ref.ref_num ref_num, ref.name refname, couns.name counsname,couns.surname counssurname, max(met.meeting_date) meetdate
FROM meetings met, refs ref,counsellors couns
WHERE met.ref_cpr_num=ref.ref_cpr_num
AND couns.counsellor_id=met.counsellor_id
AND max(met.meeting_date) < (date_sub( curdate( ) , interval 90 day))
GROUP BY ref.ref_num
there is a problem with the line AND max(met.meeting_date) - is it possible to make it in a single query?
Thanks...
problem with group by query
Moderator: General Moderators
You likely need to 'GROP BY' all fields that you are selecting.
ref.ref_num (you got allready), ref.name, couns.name and so on...
But I'm not entierly sure on this (I come from a MSSql enviroment where grouping every field selected is a must in certain cases).
Continuing; Depending on SQL version, you could either use subqueries or temp tables:
http://www.mysql.com/doc/en/example-Max ... p-row.html
Good Luck...
ref.ref_num (you got allready), ref.name, couns.name and so on...
But I'm not entierly sure on this (I come from a MSSql enviroment where grouping every field selected is a must in certain cases).
Continuing; Depending on SQL version, you could either use subqueries or temp tables:
http://www.mysql.com/doc/en/example-Max ... p-row.html
Good Luck...