Solved

Problem with INNER JOIN

Posted on 2008-06-26
1
303 Views
Last Modified: 2008-06-26
Hi all,
I have Two tables : "Users" & "UserLogs"
The table Users has all the common fields (ID, Name, Email, etc....)
The table UserLogs has: UserID, UserLogTypeID, CreatedDate.

The point is that if a user for example LogsOn to the website, the system will insert to the UserLogs Table this row:
UserID: 29 (just for the example)
UserLogTypeID: 1 (1 means "LogOn", 2 means "LogOff", etc......)
CreatedDate = now() / GatDate() / etc... :)

OK.
If I want to see what is the last time the user was logged on, I make this query:

SELECT     TOP (1) CreatedDate
FROM          UserLogs
WHERE      (UserLogTypeID = 1) AND (UserID=29)
ORDER BY CreatedDate DESC


Until here it's all good.

But, I want to pull all the users table and insert an extra field called LastLoggedOn.
I try to do this query but I get the results for only the last user and all the others are NULL:

SELECT     Users.ID, Users.Email,  Users.FirstName, Users.LastName, tblLastLogon.CreatedDate AS LastLogon
FROM         Users LEFT INNER JOIN
                          (SELECT     TOP (1) CreatedDate, UserID
                            FROM          UserLogs
                            WHERE      (UserLogTypeID = 1)
                            ORDER BY CreatedDate DESC) AS tblLastLogon ON Users.ID = tblLastLogon.UserID

Please tell me how to fix it.
Thanks
0
Comment
Question by:giligatech
1 Comment
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21873258
here we go:
SELECT     u.ID, u.Email,  u.FirstName, u.LastName, ul.CreatedDate AS LastLogon
FROM         Users u
LEFT OUTER JOIN  UserLogs ul
  ON ul.userid = u.id
 AND ul.createddate = ( SELECT MAX(i.creteddate) FROM UserLogs i WHERE i.UserID = u.ID )

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now