Join table and get only one row from other table

[LLLLLLL]

This seems like something I've done several times a year for years, but I can't seem to get it. I can't find a solution on Google that works, although the question was asked many times and answered, too. Also, I'm low on sleep right now.

 

Let's say I have two tables:

USERS

USERS_LOGINS

 

I want to join the tables and get one row per user, with only the latest login row. Assume "UserId" is the pk/fk and there's some sort of LoginTime in the USERS_LOGINS table.

 

So I will get results like:

- Brian  8/4

- Mitch 8/3

- Jerry 8/2

 

I appreciate the help.

[Barand]

SELECT 

  u.name

, MAX(DATE(ul.login_time)) as latest

FROM USER_LOGINS ul

INNER JOIN USERS u USING(userid)

GROUP BY u.name

Edited August 18, 2017 by Barand

[LLLLLLL]

But what if I want everything from users (u.*), and just the one column from logins? This seems to be where I'm getting duplicate rows.

[requinix]

Then

SELECT 
  u.*
, MAX(DATE(ul.login_time)) as latest
FROM USER_LOGINS ul
INNER JOIN USERS u USING(userid)
GROUP BY u.name

though I would

GROUP BY u.userid

[LLLLLLL]

Oops, I'm sorry I messed had some extra stuff in a third table that messed up the query.

Edited August 24, 2017 by timneu22

[Psycho]

Create a dynamic table with the user_id and last login time for each user. Use that table as an intermediary to join each user record with the user last login record.

 

 

SELECT u.*, ul.*
 
FROM users u
 
JOIN (
    SELECT user_id, MAX(login_time) as last_login
    FROM user_logins
    GROUP BY user_id
    ) as i 
  ON i.user_id = u.user_id
 
JOIN user_logins ul
 ON ul.user_id = i.user_id
    AND ul.login_time = i.last_login