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

Just for the sake of showing one more way to solve the problem, the "longer, messier, and slower" JOIN method that (I thought) MatBailie didn't show would look like this:

SELECT TOP (1) WITH TIES
  e.ID
 ,l.EmpID
 ,e.Name
 ,l.LoginTime
FROM
  dbo.Employee AS e
  JOIN
  dbo.LoginEntry AS l
    ON 
      l.EmpID = e.ID
ORDER BY 
  ROW_NUMBER() OVER (PARTITION BY e.ID ORDER BY l.LoginTime DESC, ID DESC)

The ROW_NUMBER in the ORDER BY clause takes all of the logins for each Employee ID and numbers them by LoginTime with the most recent first (and using the LoginEntry ID as tie breaker, and thanks for that touch, Mat).

Then, the SELECT TOP (1) WITH TIES does its thing. The WITH TIES bit means that it selects the number one result from each PARTITION BY group in the ORDER BY clause.

Answer 2

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.

Answer 3

you can use window function row_number()

    with cte as(    
SELECT e.ID,l.EmpId,Name,l.LoginTime, row_number() over(partition by e.ID order by l.LoginTime) as rn
FROM Employee e join LoginEntry l  on l.EmpId=e.id
    ) select ID,EmpId,Name,LoginTime from cte where rn=1

demo link