How Result using cross apply, can achieve using any other joins like inner,left,right join in sql server

Question

This is my SQL script with sample data

CREATE TABLE [dbo].[Employee]
(
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [Name] [VARCHAR](100) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[LoginEntry]
(
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [LoginTime] [DATETIME] NULL,
    [EmpID] [INT] NULL,
    [GateNumber] [VARCHAR](50) NULL
) ON [PRIMARY]
GO

ALTER TABLE Employee 
    ADD CONSTRAINT Pk_Employee PRIMARY KEY (Id)
GO

ALTER TABLE LoginEntry 
    ADD CONSTRAINT Fk_LoginEntry_Employee 
        FOREIGN KEY (EmpId) REFERENCES Employee(Id)
GO

SET IDENTITY_INSERT [dbo].[Employee] ON 
GO

INSERT [dbo].[Employee] ([ID], [Name]) 
VALUES (1, N'Employee 1'), (2, N'Employee 2'), (3, N'Employee 3'),
       (4, N'Employee 4'), (5, N'Employee 5'), (6, N'Employee 6')
GO

SET IDENTITY_INSERT [dbo].[Employee] OFF
GO


SET IDENTITY_INSERT [dbo].[LoginEntry] ON 
GO

INSERT [dbo].[LoginEntry] ([ID], [LoginTime], [EmpID], [GateNumber]) 
VALUES (1, CAST(N'2014-10-24 08:00:00.000' AS DateTime), 1, N'Gate 1'),
       (2, CAST(N'2014-10-24 09:00:00.000' AS DateTime), 1, N'Gate 1'),
       (3, CAST(N'2014-10-24 10:00:00.000' AS DateTime), 1, N'Gate 2'),
       (4, CAST(N'2014-10-24 08:00:00.000' AS DateTime), 2, N'Gate 1'),
       (5, CAST(N'2014-10-24 09:00:00.000' AS DateTime), 2, N'Gate 1'),
       (6, CAST(N'2014-10-24 10:00:00.000' AS DateTime), 2, N'Gate 2'),
       (7, CAST(N'2014-10-24 08:00:00.000' AS DateTime), 3, N'Gate 1'),
       (8, CAST(N'2014-10-24 09:00:00.000' AS DateTime), 3, N'Gate 1'),
       (9, CAST(N'2014-10-24 10:00:00.000' AS DateTime), 3, N'Gate 2'),
       (10, CAST(N'2014-10-24 08:00:00.000' AS DateTime), 4, N'Gate 1'),
       (11, CAST(N'2014-10-24 09:00:00.000' AS DateTime), 4, N'Gate 1'),
       (19, CAST(N'2014-10-24 08:00:00.000' AS DateTime), 5, N'Gate 1'),
       (20, CAST(N'2014-10-24 09:00:00.000' AS DateTime), 5, N'Gate 1'),
       (21, CAST(N'2014-10-24 10:00:00.000' AS DateTime), 5, N'Gate 2'),
       (22, CAST(N'2014-10-24 08:00:00.000' AS DateTime), 6, N'Gate 1'),
       (23, CAST(N'2014-10-24 09:00:00.000' AS DateTime), 6, N'Gate 1'),
       (24, CAST(N'2014-10-24 10:00:00.000' AS DateTime), 6, N'Gate 2')

SET IDENTITY_INSERT [dbo].[LoginEntry] OFF
GO


SELECT 
    e.ID, dt.EmpId, Name, LoginTime
FROM 
    Employee e
CROSS APPLY 
    (SELECT TOP 1
         l.ID, l.LoginTime, l.EmpId
     FROM 
         LoginEntry l 
     WHERE 
         l.EmpId = e.id) dt
GO

The result I get:

ID  EmpId   Name            LoginTime
-----------------------------------------------
1   1       Employee 1  2014-10-24 08:00:00.000
2   2       Employee 2  2014-10-24 08:00:00.000
3   3       Employee 3  2014-10-24 08:00:00.000
4   4       Employee 4  2014-10-24 08:00:00.000
5   5       Employee 5  2014-10-24 08:00:00.000
6   6       Employee 6  2014-10-24 08:00:00.000

I am Expecting the same result using Joins(inner,right,left,full) in sql server i tried my luck but couldn't, pls can any one help me out thanks in advance

Answer 1

First, your query is incomplete. When you use TOP 1 without an ORDER BY you never have a guarantee which one it will pick. New data, concurrent processes, re-indexing, software patches, the time of day, they all can cause the result to change.

So, it should be something like...

SELECT
  e.ID,dt.EmpId,Name,LoginTime
FROM
  Employee e
CROSS APPLY
(
  SELECT TOP 1
    l.ID
   ,l.LoginTime
   ,l.EmpId
  FROM
    LoginEntry l
  WHERE
    l.EmpId=e.id
  ORDER BY
    l.LoginTime DESC   -- Will cause TOP 1 to pick the most recent value (per employee)
)
  dt

As for doing it with joins, doing the TOP 1 (or greatest-n-per-group, for which your n is 1), is longer, messier, and slower. So I won't go in to that.

But you can use ROW_NUMBER() to do the TOP 1 part, and then use a JOIN to associate that result with your main table...

WITH
  ordered_logins AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY LoginTime DESC, ID DESC) AS row_ordinal
  FROM
    LoginEntry
)
SELECT
  e.ID, l.EmpId, e.Name, l.LoginTime
FROM
  Employee e
LEFT JOIN
  ordered_logins l
    ON  l.EmpID = e.ID
    AND l.row_oridnal = 1

The ROW_NUMBER() assigns every row a value from 1 upwards (per EmpID - the partition clause). It's order by loginTime descending, so the latest logins are first, and just in case two logins have the exact same time, it's secondarily ordered by ID desc.

Then the LEFT JOIN only picks the rows numbered 1 (the latest logins) and if there are no logins gives NULLs instead (so that the employee record is Not discarded due to a lack of join).

Note: the LEFT JOIN equivalent for APPLY is to use OUTER APPLY instead of CROSS APPLY.