July 12th, 2013, 09:18 AM
I have tried to simplify the example query as much as possible for this question.
I have a table called person which holds people.
I have another table which holds records of attendance.
The goal is to produce a report which will list the people in the person table as well as showing if they were present at the three most recent events.
The final query is intended to list the 6 most recent events. My main question is whether or not I can somehow reference the dates returned in the sub query using limit 0,6 rather than re-running the subquery each time with limits 0,1, 1,1 and 2,1 etc.
date_a.present as date_a_present,
date_b.present as date_b_present,
date_c.present as date_c_present,
left join attendance as date_a
on person.personID=date_a.personID and
left join attendance as date_b
on person.personID=date_b.personID and
left join attendance as date_c
on person.personID=date_c.personID and
Last edited by TASB; July 12th, 2013 at 09:35 AM.
July 12th, 2013, 02:36 PM
JOIN attendance AS date_a
ON date_a.personID = person.personID
AND date_a.attendanceDate IN
( SELECT DISTINCT
BY attendanceDate DESC LIMIT 6 )
HAVING COUNT(*) = 6