Jump to content

Join table and get only one row from other table


LLLLLLL
 Share

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 comment
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 comment
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.

 Share

×
×
  • 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.