Summing condition excludes rows without positive sum

I’m having a weird issue where in returning the sum of rows where a condition is true seems to only return rows if that condition is true – or at least, it is excluding a large majority of the rows that would otherwise be returned.

Original query – which returns all rows:

SELECT r.type, r.user1, r.user2, r.roomkey, r.name, r.active, m.active
FROM members as m 
INNER JOIN rooms as r ON m.roomid=r.id 
WHERE m.userid = ? 
ORDER BY r.active DESC, m.active DESC

For each row that was returned, I also wanted to get the number of records in another table with a newer timestamp than one of the timestamps I’ve already queried.

Modified query:

SELECT r.type, r.user1, r.user2, r.roomkey, r.name, r.active, m.active,
  COUNT(e.datetime > m.active) AS s 
FROM members as m 
INNER JOIN rooms as r ON m.roomid = r.id 
LEFT JOIN messages as e ON r.id = e.roomid 
WHERE m.userid = ? 
ORDER BY r.active DESC, m.active DESC

Basically, the original query returns all of the chat rooms a user is in. I want to modify the query to also return the number of unread messages corresponding to that room.

I’m trying to do it in a single query, so I don’t have to execute another query in the while loop. Is there a better approach than the one above? I’m mainly worried about performance here, which is why I wanted to cram it all into a single query, but it doesn’t seem to be working out. I’ve already confirmed that HAVING would not help me here and I had a similar lack of luck with subqueries.

Answer

Your query is not valid (although MySQL might accept it), since you have an aggregation function in the SELECT clause but no GROUP BY clause. Also, the additional JOIN will evict rooms that have no messages, which, presumably, s not what you want.

You could use a LEFT JOIN and aggregate in the outer query, however I would recommend a correlated subquery instead:

SELECT 
    r.type, 
    r.user1, 
    r.user2, 
    r.roomkey, 
    r.name, 
    r.active,
    m.active,
    (
        SELECT COUNT(*) 
        FROM messages e 
        WHERE r.id = e.roomid AND e.datetime > m.active
    ) no_messages
FROM members as m 
INNER JOIN rooms as r ON m.roomid = r.id 
WHERE m.userid = ? 
ORDER BY r.active DESC, m.active DESC

For performance with this query, you want an index on messages(room_id, datetime).