giligatech
asked on
Problem with INNER JOIN
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.