In MySQL User table and Login history log table with Left join gives an error for contains nonaggregated column I am trying to get all user information data that is on the USER table and also only the user's last login information(which is on another table called LOGIN_HISTORY).
I tried with Left join but MySQL is giving me the error which shows &quot;Expression #15 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'login_history.user_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by&quot;
Here is the USER table

Please help!
and this is my LOGIN_HISTORY table.

The output which I want is

I have tried this query, I don't know what I am missing but it is not giving me the desired output. I only want all the user information with his LAST login_datetime column. I tried the below query but it is showing all the user records with duplicate and all the login_datetime.
I tried:
select u.id, u.firstname, u.lastname, u.email, u.username, u.password, lh.login_datetime 
from user u 
left join login_history lh on u.id = lh.user_id 
group by u.id  
order by DATE(lh.login_history) desc;`

Please help!
An alternative method to that suggested above would be to do the following (fiddle here):
CREATE TABLE user
(
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR (256) NOT NULL
);

The number of fields is irrelevant, since you won't be grouping by them. This is just a sample for demonstration purposes.
Then:
CREATE TABLE login_history
(
  lh_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id INTEGER NOT NULL,
  login_dt TIMESTAMP NOT NULL,
  
  CONSTRAINT lh_user_dt_uq UNIQUE (user_id, login_dt),  -- same user can't login at the same time
  
  CONSTRAINT lh_uid_fk FOREIGN KEY (user_id) REFERENCES user (id)
  
);

Populate:
INSERT INTO user (email)
VALUES
('111@xyz.com'),
('222@xyz.com'),
('333@xyz.com'),
('444@xyz.com'),
('555@xyz.com'),
('666@xyz.com');

and:
INSERT INTO login_history (user_id, login_dt)
VALUES
(1, '2011-01-11'), (1, '2011-02-11'), (1, '2011-03-11'),
(2, '2012-01-12'), (2, '2012-02-12'), (2, '2012-03-12'),
(3, '2013-01-13'), (3, '2013-02-13'), (3, '2013-03-13'),
(4, '2014-01-14'), (4, '2014-02-14'), (4, '2014-03-14'), 
(5, '2015-01-15'), (5, '2015-02-15'), (5, '2015-03-15');

And the query would be:
SELECT u.*, tab.lh_id, tab.ldt
FROM user u
JOIN
(
  SELECT 
    ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
    lh_id,
    lh.user_id AS id,
    lh.login_dt AS ldt
  FROM login_history lh
) tab
ON u.id = tab.id
WHERE tab.rn = 1
ORDER BY u.id;

Result:
id  email        lh_id  ldt
1   111@xyz.com     3   2011-03-11 00:00:00
2   222@xyz.com     6   2012-03-12 00:00:00
3   333@xyz.com     9   2013-03-13 00:00:00
4   444@xyz.com    12   2014-03-14 00:00:00
5   555@xyz.com    15   2015-03-15 00:00:00

which, by inspection, is correct!
Now, in the fiddle, I've done a performance analysis of the query proposed above and @Akina's answer - and you'll see that my query is less performant. &quot;So, what's the point?&quot; you might ask?
Well, imagine a scenario where you want the last 2 logins or 3 or the last x of them... - just change the desired values of rn (put tab.rn &lt;= 2 or tab.rn IN (1,2)- shown at bottom of fiddle)!
Using MySQL's window functions, in this case the ROW_NUMBER() function (examples here), you can answer many more questions of your data.
I would strongly urge you to become familiar with window functions like those used in the second piece of SQL - they are very powerful and well worth the effort to learn - they will repay that effort 10 times over...
EDIT:
Following a query by the OP concerning users who have never logged in, I did the following (fiddle - same as above).
In order to JOIN the user table to the login_history table with a field that doesn't exist in the login_history, we have to do a LEFT [OUTER] JOIN (the left join is automatically an outer one, so the OUTER keyword is optional).
I added a user (6, 666@xyz.com) (see above) who has no records in the login table.
Then, I used the following SQL:
SELECT 
  u.id,
  u.email,
  ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
  lh.lh_id,
  lh.user_id AS luid,
  lh.login_dt AS ldt
FROM user u
LEFT JOIN login_history lh
ON u.id = lh.user_id
ORDER BY u.id DESC;

Result (snipped for brevity):
id  email         rn    lh_id   luid    ldt
6   666@xyz.com     1    NULL    NULL   NULL    
5   555@xyz.com     1      15       5   2015-03-15 00:00:00
5   555@xyz.com     2      14       5   2015-02-15 00:00:00
...
...

So, we see that through the LEFT JOIN we now have the user 6, but the corresponding records from the login history are NULL (as expected)!
Now we simply wrap the above query in another to get the latest (or 2 latest or 3...) login time(s) for a given user as follows:
SELECT * FROM
(
  SELECT 
    u.id,
    u.email,
    ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
    lh.lh_id,
    lh.user_id AS luid,
    lh.login_dt AS ldt
  FROM user u
  LEFT JOIN login_history lh
  ON u.id = lh.user_id
) AS tab
WHERE tab.rn = 1  -- or tab.rn IN (1,2) - shown at the bottom of the fiddle
ORDER BY tab.id;

Result:
id  email          rn  lh_id    luid    ldt
1   111@xyz.com     1      3       1    2011-03-11 00:00:00
2   222@xyz.com     1      6       2    2012-03-12 00:00:00
3   333@xyz.com     1      9       3    2013-03-13 00:00:00
4   444@xyz.com     1     12       4    2014-03-14 00:00:00
5   555@xyz.com     1     15       5    2015-03-15 00:00:00
6   666@xyz.com     1   NULL    NULL    NULL

I also added the results for EXPLAIN ANALYZE (available from MySQL &gt;= 8.0.18), and I also profiled the query (fiddle).
As with the previous solution using the ROW_NUMBER() window function, it's a bit slower and the plan is more complex but, as I said, it is also potentially far more powerful down the line. I would urge you to test with your own system (H/ware &amp; S/ware) and data to see which solution best suits your requirements.