mysql table join with aggregates

0

votes

I have 2 tables. Person table and activity_log table

There are many activities for each person.

I would like to "select" a list of "persons" showing the activity_name and activity_date of their last activity. (not all activities)

Person table has person_id, name, email

Activity table has person_id, activity_id, activity_date, activity_name

Thanks for your help in advance.

mysqlsqlselect

1

votes

A subquery should do the trick:

SELECT p.person_id, p.name, p.email, a.activity_id, a.activity_date, a.activity_name
FROM Person p
LEFT JOIN Activity a
ON p.person_id = a.person_id
WHERE a.activity_date = (SELECT MAX(a1.activity_date)
               FROM activity a1
               WHERE a.person_id = a1.person_id
               GROUP BY person_id) OR activity_date IS NULL;

1

votes

Assuming your IDs are auto numbered, you'd do like this:

SELECT pe.person_id,
       pe.name,
       al.activity_name,
       al.activity_date
FROM   person pe
       LEFT JOIN (SELECT p.person_id,
                         Max(a.activity_id) activity_id
                  FROM   person p
                         LEFT JOIN activity_log a
                           ON ( p.person_id = a.person_id )
                  GROUP  BY p.person_id) AS LAST
         ON pe.person_id = LAST.person_id
       LEFT JOIN activity_log al
         ON LAST.activity_id = al.activity_id

However, users might enter past activities later than newer ones, then this will fail and you'd have to go like this:

SELECT LAST.person_id,
       LAST.name,
       LAST.activity_date,
       (SELECT activity_name
        FROM   activity_log al
        WHERE  al.person_id = LAST.person_id
               AND al.activity_date = LAST.activity_date) activity_name
FROM   (SELECT p.person_id,
               Max(p.name)          AS name,
               Max(a.activity_date) activity_date
        FROM   person p
               LEFT JOIN activity_log a
                 ON ( p.person_id = a.person_id )
        GROUP  BY p.person_id) AS LAST

But this still has a problem: since MySQL does not allow LIMIT in sub-queries, the query will fail if the same person has two activities with the same activity_date and that's the latest date.

mysql table join with aggregates

2 Answers