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

mysql join 3 tables
mysql full outer join
mysql multiple joins
inner join
mysql outer join
mysql join types
mysql left join example
sql join where

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.

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

MySQL 8.0 Reference Manual :: 13.2.10.2 JOIN Clause, In the first SELECT statement, column j appears in both tables and thus becomes a join column, so, according to standard SQL, it should appear only once in the� Summary: in this tutorial, you will learn various MySQL join clauses in the SELECT statement to query data from two tables.. Introduction to MySQL join clauses. A relational database consists of multiple related tables linking together using common columns which are known as foreign key columns.

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

SQL Joins, The relationship between the two tables above is the "CustomerID" column. Then , we can create the following SQL statement (that contains an INNER JOIN),� Different Types of SQL JOINs. Here are the different types of the JOINs in SQL: (INNER) JOIN: Returns records that have matching values in both tables; LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table

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 

Using MySQl Joins, You can use multiple tables in your single SQL query. The act of joining in MySQL refers to smashing two or more tables into a single table. You can use JOINS in the SELECT, UPDATE and DELETE statements to join the MySQL tables. We will see an example of the LEFT JOIN also which is different from the simple MySQL JOIN. Summary: in this tutorial, you will learn how to use the MySQL INNER JOIN clause to select data from multiple tables based on join conditions. Introduction to MySQL INNER JOIN clause. The INNER JOIN matches each row in one table with every row in other tables and allows you to query rows that contain columns from both tables.

MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS, Simply use of single JOIN query instead running multiple queries do reduce server overhead. Using multiple queries instead that leads more data� MySQL JOINS. MySQL JOINS are used with SELECT statement. It is used to retrieve data from multiple tables. It is performed whenever you need to fetch records from two or more tables. There are three types of MySQL joins: MySQL INNER JOIN (or sometimes called simple join) MySQL LEFT OUTER JOIN (or sometimes called LEFT JOIN)

MySQL: Joins, MySQL JOINS are used to retrieve data from multiple tables. A MySQL JOIN is performed whenever two or more tables are joined in a SQL statement. There are � INNER JOIN table2. ON table1.column_name=table2.column_name; Inner Join and simple join both are same. You can also write your query like this: SELECT column_name(s) FROM table1. JOIN table2. ON table1.column_name=table2.column_name; Now let us fetch the Name and the message from our database using Inner join. The query will be like this

MySQL JOINS, MySQL JOINS: JOIN clauses are used to return the rows of two or more queries using two or more tables that shares a meaningful relationship� You can use a JOIN SELECT query to combine information from more than one MySQL table. With JOIN, the tables are combined side by side, and the information is retrieved from both tables. Tables are combined by matching data in a column — the column that they have in common. The combined results table produced […]

Comments
  • This won't work properly since there is no GROUP BY. Without that you will and up doing a count on everything and not by user.
  • This is for a specific user, like the OP wants.
  • For the sake of simplicity, I choose this one. Thanks.
  • Do you actually need distinct on an id?
  • There is a left join that may fetch the same id more than once.
  • Without using an aggregate on nDevice and max_time, MySQL will select one of the values in the joined table at random rather than getting the total or the most recent value. name is also not a good column to group by since multiple users could have the same name.
  • @drakin8564 .. correct .. you could post a valid answer too .anyway answer updated enforcing the group by using id