Jump to content
LLLLLLL

Join table and get only one row from other table

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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 by Barand

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Edited by timneu22

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.