CODEWITHSUNDEEP
mysql
Gowthamr.cs
Gowthamr.cs

Reputation: 141

Mysql Query to find last logged in date of each user

I have two tables tbl_user and tbl_lastlogin as shown below

Table tbl_user 

id|  name 
---------
1 | user1 
2 | user2 
3 | user3

Table tbl_lastlogin 

id | loginid | date_lastlogin
----------------------------
1  |    1    |  2014-03-28
2       1       2014-03-29
3       2       2014-02-14
4       2       2014-02-15

Relation of Above table (tbl_user.id = tbl_lastlogin.loginid)

How do I get output as. 

id | name| date_lastlogin
---------------------
1  |user1 | 2014-03-29 
2  |user2 | 2014-02-15
3  |user3 | null

I need most recently logged in date of each user.

Thank you.

Upvotes: 1

Views: 8369

Answers (4)

Sadikhasan
Sadikhasan

Reputation: 18600

Try this

SELECT U.id,U.name,UL.date_lastlogin FROM  tbl_user U 
LEFT JOIN tbl_lastlogin UL ON U.id=UL.loginid 
GROUP BY UL.loginid 
ORDER BY UL.date_lastlogin DESC

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You need to join your tables and get max of last login date 

SELECT u.id,u.name,MAX(l.date_lastlogin) date_lastlogin
FROM tbl_user u
LEFT JOIN tbl_lastlogin l ON(u.id=l.loginid )
GROUP BY u.id

Upvotes: 2

Roberto
Roberto

Reputation: 9090

If you only need the last login date you can use an embbeded select into main select-list:

select id, name, 
       (select max(date_lastlogin) 
           from tbl_lastlogin ll 
           where ll.loginid = u.id) as 'date_lastlogin` 
from tbl_user u;

Just an advice, the name tbl_lastlogin is not too much addecuate, becasue It stores the login history not only the last one, maybe tbl_loginhistory or something like that

Upvotes: 1

Dmitri
Dmitri

Reputation: 2467

LEFT OUTER JOIN and GROUP BY will help you, I believe.

Upvotes: 0

Related Questions