Aggregating join results that I also use in the where clause with MySQL

Question

I'm referencing two tables in the simple machines forum web forum software: smf_members, and smf_members. each row in smf_members has a field: id_group, of which I am interested in values: 1,9,20,26,23,27 (using the IN() clause). The general goal is to determine which rows in the smf_members table that are in the above group id's haven't had a row entered in smf_messages in 90 days. poster_time in smf_messages is a unix timestamp. What I have so far is:

SELECT
  m.id_member,
  m.id_group,
  from_unixtime(max(ms.poster_time))
FROM
  smf_members m
  LEFT JOIN smf_messages ms
    USING(id_member)
WHERE
  max(ms.poster_time) < (NOW() < (86400 * 90)
GROUP BY
  m.id_member

It fails with and ERROR 1111: Invalid use of the group function, since I am using max() in the where clause. How can I aggregate my join results to only reference the latest entry based off the poster_time field?

Answer 1

There are several ways to this.

1. SELECT * FROM members WHERE id_member IN ( ... ) AND NOT EXISTS ( SELECT 1 FROM messages WHERE is_member = members.id_member AND poster_time > (UNIX_TIMESTAMP*() - 90 * 24 * 60 * 60)

2. look for the cases where an outer join finds nothing SELECT DISTINCT id_member FROM MEMBERS AS m ... LEFT JOIN messages AS msg USING .... WHERE msg.id IS NULL

3. SELECT * FROM members WHERE id_member IN ( .... ) AND id_member NOT IN (SELECT id_member FROM messages WHERE poster_time > (UNIX_TIMESTAMP*() - 90 * 24 * 60 * 60) )