Problem with INNER JOIN
Posted on 2008-06-26
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... :)
If I want to see what is the last time the user was logged on, I make this query:
SELECT TOP (1) CreatedDate
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
WHERE (UserLogTypeID = 1)
ORDER BY CreatedDate DESC) AS tblLastLogon ON Users.ID = tblLastLogon.UserID
Please tell me how to fix it.