What Am I Missing? MySQL Left Join Most Newest Entry From 2nd Table

Question

I need a fresh pair of eyes on this. I have two tables, one of which has users and the second which contains login records, multiple records for each user. What I'm trying to do is select all entries from the first table, and the most recent record from the second table, e.g., a list of all users but only show the most recent activity. Both tables have auto increment in the ID column.

My code currently is thus:

SELECT u.user_id, u.name, u.email, r.rid, r.user_id
FROM users AS u
LEFT JOIN login_records AS r ON r.user_id = u.user_id
WHERE
   r.rid = (
      SELECT MAX( rid )
      FROM login_records
      WHERE user_id = u.user_id
   )

I've scoured answers to similar questions on SO and tried all of them, but results have been either returning nothing or only getting odd results (not necessarily the newest one). ID in both tables is auto-increment, so I thought it should be a relatively simple matter to get the only or highest ID for a particular user, but it either returns nothing or a completely different selection each time.

It's my first time using JOIN - do I have the wrong JOIN? Do I need to ORDER or GROUP things differently?

Thanks for your help. It's got to be something simple, since Danny Coulombe's answer appearing here seems to work for other users.

I think it should be -- LEFT JOIN login_records AS r ON u.user_id = r.user_id as the join will fetch data from the table on the left of the JOIN condition. — Venkat D, Sep 08 '18 at 19:16

score 1 · Accepted Answer · answered Sep 08 '18 at 19:32

You will need a subquery I believe:

https://www.db-fiddle.com/f/2wudMDVxReYJz4FEyG19Va/0

CREATE TABLE users (
user_id  INT UNSIGNED NOT NULL 
  AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE users_logins (
user_login_id  INT UNSIGNED NOT NULL 
  AUTO_INCREMENT PRIMARY KEY,
  user_id  INT UNSIGNED NOT NULL 
);

INSERT INTO users SELECT 1;
INSERT INTO users SELECT 2;

INSERT INTO users_logins SELECT 1,1;
INSERT INTO users_logins SELECT 2,1;
INSERT INTO users_logins SELECT 3,1;
INSERT INTO users_logins SELECT 4,1;
INSERT INTO users_logins SELECT 5,2;
INSERT INTO users_logins SELECT 6,2;

And the query:

SELECT 
u.user_id, ul.latest_login_id
FROM users u
    LEFT JOIN
    (
      SELECT user_id, MAX(user_login_id) latest_login_id
      FROM users_logins
      GROUP BY user_id
    ) ul ON u.user_id = ul.user_id

You sir are awesome...that did the trick. I've gone through things trying to normalize my tables, and it's worked okay up until this which has kept me stumped for 4 days...thank you for your help! — RalphTheWonderLlama, Sep 08 '18 at 20:50

LukStorms · Answer 2 · 2018-09-08T20:10:24.320

0

How about replacing all rid in where clause and corrolated subquery by record_id?

SELECT u.user_id, u.name, u.email, r.rid, r.record_id, r.user_id
FROM test_users AS u
LEFT JOIN test_login_records AS r ON r.user_id = u.user_id
WHERE 
 (r.record_id = (
   SELECT MAX(record_id)
   FROM test_login_records
      WHERE user_id = u.user_id
  ) OR r.record_id is null);

Test here

edited Sep 08 '18 at 20:10

answered Sep 08 '18 at 19:24

LukStorms

28,916
5
31
45

This will leave out all users that never logged in. If this desireable - then query ahoi! – madflow Sep 08 '18 at 20:04
@madflow Heh, you're right. Well spotted. Those won't have a max record_id. Oh well, that's easy to fix. – LukStorms Sep 08 '18 at 20:10

score 0 · Answer 3 · answered Sep 08 '18 at 19:24

0

You have to ORDER BY with what column you want to display by desc, for example ORDER BY last_login DESC.

Change the last_login column with the column you want to order, but you must first declare the last_login column after SELECT.

answered Sep 08 '18 at 19:24

Syahrul

13
8

What Am I Missing? MySQL Left Join Most Newest Entry From 2nd Table

3 Answers3