I wish I could help you further, but since I'm new to the featureful SQL world (I have been using MySQL for too long) I don't know the specifics of how to do this. But here is my shot at it anyways:
SELECT * FROM person
LEFT JOIN
( SELECT personid,MAX(visittime) FROM visit GROUP BY personid )
AS lastvisit ON ( person.personid = lastvisit.personid );
Should get you somewhere close.
If you are stuck in a DB engine that doesn't support subselects, you could either select into a temporary table and then select out of that (the MySQL way), or you could just process the list directly and figure it out for yourself.
Another possible option, if this is a frequent query, is to un-normalize that data, and store the last visit time (or id, depending on what you want to do) in the subject record.
|