Jump to content

Join table and get only one row from other table


Go to solution Solved by Barand,

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.

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
Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.