Posted 18 August 2017 - 05:40 PM
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:
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.
Posted 18 August 2017 - 05:53 PM Best Answer
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, 18 August 2017 - 05:55 PM.
Donations gratefully received
|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
Posted 24 August 2017 - 04:57 AM
Posted 24 August 2017 - 01:51 PM
Oops, I'm sorry I messed had some extra stuff in a third table that messed up the query.
Edited by timneu22, 24 August 2017 - 02:03 PM.
Posted 24 August 2017 - 01:52 PM
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
I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users