CODEWITHSUNDEEP
mysqlsqldatabasejoincount
andcl
andcl

Reputation: 3548

MySQL query with join + count for a specific user in DB

I want to get the sum of several entities from several tables using a query. Specifically, I want the devices and messages count of a user. In addition, the timestamp of the last received message for that user. I have 3 tables:

Users

id     name     (other fields)
1      Mike     ...
2      John     ...
3      Yay      ...
4      Jim      ...

Devices

id     user_id   (other fields)
1      1         ...
2      1         ...
3      1         ...
4      2         ...

Messages

id     device_id   message    time                   (other fields)
1      1           Hi         2019-04-07 12:06:44    ...
2      1           Hey        2019-04-06 12:06:44    ...
3      2           Sup        2019-04-05 12:06:44    ...
4      3           Ok         2019-04-04 12:06:44    ...
5      4           Yay        2019-04-08 12:06:44    ...

... and, for example, for user Mike I want to end up with:

Result 

nDevices    nMessages      time 
3           4              2019-04-07 12:06:44

Any ideas? Thanks in advance.

Upvotes: 0

Views: 63

Answers (3)

ScaisEdge
ScaisEdge

Reputation: 133360

You could try using a join between user and device and a inner join with the subquery for message and device 

select a.name, a.id,  count(d.user_id), t.nDevice, t.max_time 
from Users a 
inner join  device d on a.id = d.user_id 
inner join  (
  select m.device_id, count(m.device_id) nDevice, max(m.time) max_time
  from Messages  m
  group by m.device_id
) t on t.device_id = d.id 
group by a.name, a.id

Upvotes: 0

forpas
forpas

Reputation: 164089

Join the 3 tables and count distinct values of the columns:

select 
  count(distinct d.id) devicescounter, 
  count(distinct m.id) messagescounter,
  max(m.time) lastmessagetime
from users u
left join devices d on u.id = d.user_id 
left join nessages m on m.device_id = d.id
where u.name = 'Mike'

If you want the results for all users:

select 
  u.id, u.name, 
  count(distinct d.id) devicescounter, 
  count(distinct m.id) messagescounter,
  max(m.time) lastmessagetime
from users u
left join devices d on u.id = d.user_id 
left join nessages m on m.device_id = d.id
group by u.id, u.name

Upvotes: 1

derek.wolfe
derek.wolfe

Reputation: 1116

The easiest way to do this would be to get the number of messages and most recent message time for each device in a subquery.

  SELECT u.id,
         COUNT(d.id) AS num_devices,
         SUM(messages) AS num_messages,
         MAX(most_recent) AS most_recent_message
    FROM users u
    JOIN devices d ON d.user_id = u.id
    JOIN (SELECT device_id,
                 COUNT() AS messages,
                 MAX(TIME) AS most_recent_message
            FROM messages
        GROUP BY device_id) m ON m.device_id = d.id
GROUP BY u.id

Upvotes: 1

Related Questions