Select users with last login (Group By)

I have two tables, the first stores a list of users (UsersAccounts) and the second stores every attempt the users have made to login to the system (UsersLoginAttempts). The tables have the following fields:

UsersAccounts
------------------
PK_User_ID (int)
NameFirst
NameLast

UsersLoginAttempts
-------------------------
PK_LoginAttempt_ID (int) fk from PK_User_ID
UserNameAttempted
DateAttempted
FK_User_ID (when this != 0 it was a valid login)

I am trying to return a list of users along with their last successful login.  I am trying to make the result set look like tihs:

NameFirst    NameLast   LastLoginAttempt
-------------------------------------------------------
Jason           Andrews     9/14/2008 3:00 PM
April             O'Neil          1/28/2008 4:43 AM
Michael         Jackson       -
David           Waters        3/21/2008 9:15 PM

Notice how the user 'Michael jackson' doesn't have a time associated - this i sbecause he has never logged in.  Also, the other users could have logged in successfully 30 times or more, but it should only show their last successful login attempt.  Even if a user has never logged in successfully, it should show their name...

Can anyone help?  I can't find out how to do this...

Thanks,

br
volumeIIIAsked:
Who is Participating?
 
i2mentalConnect With a Mentor Commented:
Not likely. If you're using something that doesn't allow a subquery (you're not able to type the sql code?), then you're probably not going to be able to use common table expressions either.

I did have a type, well several typos in the code, you can try this instead if you want.


Select  NameFirst,
        NameLast,
        DateAttempted
from    useraccounts
        left join ( ( select    fk_user_id,
                                UserNameAttempted,
                                DateAttempted
                      from      UsersLoginAttempts
                    ) as ula
                    join ( select   fk_user_id,
                                    max(DateAttempted)
                           FROM     UsersLoginAttempts
                           group by fk_user_id
                         ) as a on ula.fk_user_id = a.fk_user_id
                  )AS b ON pk_user_id = b.fk_user_id

Open in new window

0
 
i2mentalCommented:
Try this

Select NameFirst,  NameLast,   DateAttempted
from useraccounts
left join (select fk_user_id, UserNameAttempted, DateAttempted from UsersLoginAttempts as ula
join (select fk_user_id, max(DateAttempted) group by fk_user_id) as a on ula.fk_user_id = a.fk_user_id)


0
 
volumeIIIAuthor Commented:
Unfortunately I couldn't get it to work... is it possible to make this happen without subqueries?  I am using a tool to do this and it doesn't allow sub queries. :(
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.