APPLY Clause in SQL Server 2005

select ClientId, Birthday from
(select top 10 Clnt.* from [Client] Clnt
inner join CaseClient CC on Clnt.ClientId = CC.ClientID
inner join [Case] C on C.CaseID = CC.CaseID order by Clnt.ClientID) as Result

This is the query which returns me the ClientId and Birthday; its TOP 10 Clients.

select Clnt.ClientId, Birthday, TopData.FormNumber  from
(select top 10 Clnt.* from [Client] Clnt
inner join CaseClient CC on Clnt.ClientId = CC.ClientID
inner join [Case] C on C.CaseID = CC.CaseID order by Clnt.ClientID) as Result
inner join
(select TOP (3) * from ClientSession where ClientSession.ClientID = Result.ClientId) TopData
on TopData.ClientID = Result.ClientID

What I am trying to do here is… there is multiple sessions for one client, and form that multiple I need top 3 rows and its FormNumner. The above query will syntactically right, parser will not generate any error; but it will at compile time it will throw error

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Result.ClientId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Clnt.ClientId" could not be bound.

For correlated Join; Result is not defined.

So here is the solution with SQL Server 2005's new APPLY clause. The APPLY clause let's you join a table to a table-valued-function. That let's you write a query like this:

select Result.ClientID, Result.Birthday, TopData.FormNumber from
(select top 10 Clnt.* from [Client] Clnt
inner join CaseClient CC on Clnt.ClientId = CC.ClientID
inner join [Case] C on C.CaseID = CC.CaseID order by Clnt.ClientID) as Result
CROSS/span> Apply
 fn_GetTopClientSession(Result.ClientId, 3) AS TopData

And here is the expected output

ClientID    Birthday                FormNumber
----------- ----------------------- -----------
46          1990-01-01 00:00:00.000 11094
46          1990-01-01 00:00:00.000 11062
46          1990-01-01 00:00:00.000 30211
52          1983-01-04 00:00:00.000 11159
52          1983-01-04 00:00:00.000 11155
52          1983-01-04 00:00:00.000 30190
53          2000-01-01 00:00:00.000 11154
53          2000-01-01 00:00:00.000 11158
53          2000-01-01 00:00:00.000 11157
68          2000-01-01 00:00:00.000 10104
68          2000-01-01 00:00:00.000 12168
68          2000-01-01 00:00:00.000 11215
73          1957-10-09 00:00:00.000 11137
73          1957-10-09 00:00:00.000 32464
73          1957-10-09 00:00:00.000 11150

And here is the function

CREATE FUNCTION dbo.fn_GetTopClientSession(@ClientId AS int, @n AS INT)
RETURNS TABLE
AS
RETURN
  select TOP (@n) * from ClientSession where ClientSession.ClientID = @ClientId
GO

I just put the Correlated Join query inside the Function, nothing more.

You can see the APPLY clause acts like a JOIN without the ON clause!!!

There are two flavors of APPLY clause, CROSS and OUTER. The OUTER APPLY clause returns all the rows on the left side whether they return any rows in the table-valued-function or not. The columns that the table-valued-function returns are null if no rows are returned. The CROSS APPLY only returns rows from the left side f the table-valued-function returns rows.

Notice that I'm just passing in the ClientId to the function. It returns the TOP 3 rows based on the amount of the order. Since I'm using CROSS APPLY a Cases without Client won't appear in the list. I can also pass in a number other than 3 to easily return a different number of Cases per Client. So I could list the top 5. How cool is that?!?