CODEWITHSUNDEEP
sqlsql-server
user2980316
user2980316

Reputation: 149

SQL Join with Sub Query

I'm trying to create a query that gets the most recent record with the status for each user and displays it, the required data is over three tables on a Microsoft SQL Server.

Tables:

  1. tblUser
  2. tblStatusName
  3. tblStatusLog

My SQL so far:

SELECT x.LatestTime AS 'Last Activity', FName + ' ' + LName AS 'User'
FROM tblUser,
    (SELECT UserID, MAX(ServerTime) As LatestTime
    FROM tblStatusLog
    GROUP BY UserID) AS x
WHERE tblUser.ID = x.UserID

I now need to join tblStatusName so I can link the StatusID column in tblStatusLog to the ID column in tblStatusName so I can get the Status Description, but I can't work out how to do this due to the aggregate function preventing me from doing so. The query will be ran often - every few seconds by multiple people so efficiency is desired.

I've tried something like the below, but this doesnt work due to the sub-query. 

INNER JOIN tblStatusName
ON X.StatusID = tblStatusName.ID

The desired output would be

Last Activity Time | User | Latest Status

Sorry if this is relatively basic, my SQL is a little rusty since my uni days.

I should add that tblStatusLog is a table that has the following columns:

Upvotes: 0

Views: 57

Answers (2)

Dwayne
Dwayne

Reputation: 26

The CROSS APPLY is a great approach. An alternative older method is to use virtual tables (inline view). A sample setup and query is below.

-- tblStatusName
drop table [tblStatusName]
GO
CREATE TABLE [dbo].[tblStatusName](
    [ID] [int] NULL,
    [Status] [nchar](30) NULL
) ON [PRIMARY]
GO
insert into tblStatusName values ('10', 'LOGON');
insert into tblStatusName values ('20', 'LOGOFF');

-- tblStatusLog
drop table [tblStatusLog]
GO
CREATE TABLE [dbo].[tblStatusLog](
    [ServerTime] [date] NULL,
    [UserID] [int] NULL,
    [StatusID] [int] NULL
) ON [PRIMARY]
GO
insert into tblStatusLog values ('01 FEB 2020', 1, 10);
insert into tblStatusLog values ('02 FEB 2020', 1, 20);
insert into tblStatusLog values ('03 FEB 2020', 1, 10);
insert into tblStatusLog values ('01 FEB 2020', 2, 10);
insert into tblStatusLog values ('02 FEB 2020', 2, 20);
insert into tblStatusLog values ('03 FEB 2020', 3, 10);

-- tblUser
drop table [tblUser]
GO
CREATE TABLE [dbo].[tblUser](
    [ID] [int] NULL,
    [FName] [nchar](10) NULL,
    [LName] [nchar](10) NULL
) ON [PRIMARY]
GO
insert into tblUser values (1,'FName 1',' LName 1');
insert into tblUser values (2,'FName 2',' LName 2');
insert into tblUser values (3,'FName 3',' LName 3');
GO


SELECT A.LatestTime, B.FName + ' ' + B.LName AS 'User', B.Status
FROM (
    SELECT UserID, MAX(ServerTime) As LatestTime
    FROM tblStatusLog
    GROUP BY UserID
    ) AS A,
    (
    SELECT tblStatusLog.UserID, tblUser.ID, tblUser.FName, tblUser.LName, tblStatusLog.ServerTime, tblStatusLog.StatusID, tblStatusName.Status
    FROM tblStatusLog, 
         tblStatusName,
         tblUser
    WHERE tblStatusLog.StatusID = tblStatusName.ID
      AND tblStatusLog.UserID = tblUser.ID
    ) AS B
WHERE A.UserID = B.UserID
  AND A.LatestTime = B.ServerTime
ORDER BY A.LatestTime DESC
GO

Upvotes: 1

Luuk
Luuk

Reputation: 14899

SELECT 
  x.LatestTime AS 'Last Activity', 
  FName + ' ' + LName AS 'User',
  tblStatusName.Status
FROM tblUser
CROSS APPLY 
    (SELECT TOP (1) UserID, ServerTime As LatestTime, StatusID
    FROM tblStatusLog
    WHERE tblStatusLog.UserID = tblUser.ID
    ORDER BY ServerTime DESC) AS x
INNER JOIN tblStatusName
ON x.StatusID = tblStatusName.ID
WHERE tblUser.ID = x.UserID;

Upvotes: 1

Related Questions