I have the following relevant tables:
person: PersonID, FullName, etc.
attendance: PersonID, EventID, AttendDate
I need a query to return all people who are "attending" EventID=1 today but will miss at least one day in the next week, preferably also returning the first day they will miss. (It's not really attendance of an event per se, but EventID=1 means they are traveling and I want to know who is coming back in the next week.) Hunting around SO, I found this answer that helped me get as far as producing a list of the missing dates for a specific person:
SELECT missdate FROM (
SELECT @r:= DATE_ADD( @r, INTERVAL 1 DAY ) missdate
FROM ( SELECT @r := CURDATE() ) vars, attendance
LIMIT 7
) justdates WHERE justdates.missdate NOT IN (
SELECT attendance.AttendDate FROM attendance
WHERE attendance.PersonID = 5222
AND attendance.AttendDate = justdates.missdate
)
That works like a charm. But if I try to join it with the person table and also add the criteria that they have to be currently on travel (have an attendance record for today), I can't make it work. So far I have this:
SELECT person.PersonID, person.FullName, MIN(home.missdate) AS returndate FROM person
INNER JOIN (
SELECT pt.PersonID FROM person pt
INNER JOIN attendance at ON pt.PersonID=at.PersonID
WHERE at.EventID=1 AND at.AttendDate=CURDATE()
) travel ON travel.PersonID = person.PersonID
INNER JOIN (
SELECT missdate FROM (
SELECT @r:= DATE_ADD( @r, INTERVAL 1 DAY ) missdate
FROM ( SELECT @r := CURDATE() ) vars, attendance
LIMIT 7
) justdates WHERE justdates.missdate NOT IN (
SELECT attendance.AttendDate FROM attendance
WHERE attendance.PersonID = person.PersonID
AND attendance.AttendDate = justdates.missdate
)
) home ON home.PersonID = person.PersonID
GROUP BY person.PersonID,person.FullName
But it doesn't recognize person.PersonID in the subquery's WHERE clause. How should I do this?