Merging more than one table

[Yesideez]

I've got a couple tables: users; onlinetime

 

The users table contains loads of info including userid, username, activitycount and the onlinetime contains id, userid, date, active, idle. The field userid is what ties both tables together and active and idle are both integers (seconds).

 

Where the users table has one entry per user (as is normal) the onlinetime table can have multiple entries per user.

 

What I need to do is make a query to list all the users who have activity count as 0 and the sum(active) in onlinetime below a certain number, let's say 100.

 

If the onlinetime had only one entry per user I'd be able to write the query but it's the multiple entries in the onlinetime table that's throwing me.

[Yesideez]

I did try this:

SELECT u.userid,u.username,z.active,z.idle FROM users AS u
  LEFT JOIN onlinetime AS t ON u.userid=t.userid
  LEFT JOIN (SELECT userid,SUM(active) AS active,SUM(idle) AS idle FROM onlinetime) AS z ON u.userid=z.userid

Doesn't work

 

Tried this as well to no avail:

SELECT u.userid,u.username,z.active,z.idle FROM users AS u
  LEFT JOIN (SELECT userid,SUM(active) AS active,SUM(idle) AS idle FROM onlinetime) AS z ON u.userid=z.userid ORDER BY z.active DESC LIMIT 20

[mikosiko]

try this:

SELECT u.userid,u.username,SUM(t.active),SUM(t.idle)
FROM users AS u
  LEFT JOIN onlinetime AS t ON u.userid=t.userid
WHERE u.activitycount = 0
GROUP BY u.userid
HAVING SUM(t.active) < 100