Solved

Problem with INNER JOIN

Posted on 2008-06-26
1
302 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

747 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

13 Experts available now in Live!

Get 1:1 Help Now