1

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 "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"

Here is the USER table

USER table

Please help! and this is my LOGIN_HISTORY table.

LOGIN_HISTORY table

The output which I want is

enter image description here

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!

2
  • group by u.id After this operation server must return only one row for each separate u.id value. In source tables a lot of different lh.login_datetime matches each u.id. What one value from all existing must be returned? Last? Tell this to server.
    – Akina
    May 27 at 18:54
  • I edited my answer with a solution for users who have never logged in! May 28 at 8:20
2

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. "So, what's the point?" 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 <= 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 >= 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 & S/ware) and data to see which solution best suits your requirements.

3
  • Thank you for the solution but only one thing, If any of the users doesn't have a login record and the above query is not showing that user information. Is there any way I can display that user information with null as login_dt as well?
    – Yash
    May 27 at 21:25
  • Thank you so much for your help. I will try to do by left join.
    – Yash
    May 27 at 21:39
  • Thank you. It works great and I will explore more about your given suggestions.
    – Yash
    Jun 2 at 16:25
0
select u.id, u.firstname, u.lastname, u.email, u.username, u.password, 
       MAX(lh.login_datetime) login_datetime
from user u 
left join login_history lh on u.id = lh.user_id 
group by u.id, u.firstname, u.lastname, u.email, u.username, u.password
order by login_history desc;
2
  • It is giving me the max login_datetime. But, I also want to add the login_history id column, and then it is giving me the error again. Same as I have. How can I do that?
    – Yash
    May 27 at 20:02
  • @Yash I also want to add the login_history id column Unclear. Edit your question and add desired output for shown soiurce data.
    – Akina
    May 28 at 4:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.