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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
i2mentalCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.