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

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.