A real mind bender here guys!
I have a table which basically positions users in a league:
LeagueID Stake League_EntryID UserID TotalPoints TotalBonusPoints Prize
13028 2.00 58659 2812 15 5 NULL
13028 2.00 58662 3043 8 3 NULL
13029 5.00 58665 2812 8 3 NULL
The League_EntryID is the unique field here but you will see this query returns multiple leagues that user is entered for that day.
I also have a table value function which returns the current prize standings for the league and this accepts the LeagueID as a parameter and returns the people who qualify for prize money. This is a complex function which ideally I would like to keep as the function accepting the LeagueID. The result of this is as below:
UserID Position League_EntryID WinPerc Prize
2812 1 58659 36.000000 14.00
3043 6 58662 2.933333 4.40
3075 6 58664 2.933333 4.40
Essentially what I want to do is to join the table value function to the topmost query by passing in the LeagueID to essentially update the Prize Field for that League_EntryID i.e.
SELECT * FROM [League]
INNER JOIN [League_Entry] ON [League].[LeagueID] = [League_Entry].[LeagueID]
INNER JOIN [dbo].[GetPrizesForLeague]([League].[LeagueID]) ....
I'm not sure if a CROSS APPLY would work here but essentially I believe I need to JOIN on both the LeagueID and the League_EntryID to give me my value for the Prize. Not sure on the best way to do this without visiting a scalar function which will in turn call the table value function and obtain the Prize from that.
Speed is worrying me here.
P.S. Not all League_EntryID's will exist as a part of the table value function output so maybe an OUTER JOIN/APPLY can be used?
EDIT See the query below
SELECT DISTINCT [LeagueID],
[CourseName],
[Refunded],
[EntryID],
[Stake],
d.[League_EntryID],
d.[UserID],
[TotalPoints],
[TotalBonusPoints],
[TotalPointsLastRace],
[TotalBonusPointsLastRace],
d.[Prize],
[LeagueSizeID],
[TotalPool],
d.[Position],
[PositionLastRace],
t.Prize
FROM
(
SELECT [LeagueID],
[EntryID],
[Stake],
[MeetingID],
[Refunded],
[UserID],
[League_EntryID],
[TotalPoints],
[TotalBonusPoints],
[TotalPointsLastRace],
[TotalBonusPointsLastRace],
[Prize],
[LeagueSizeID],
[dbo].[GetTotalPool]([LeagueID], 1) AS [TotalPool],
RANK() OVER( PARTITION BY [LeagueID] ORDER BY [TotalPoints] DESC, [TotalBonusPoints] DESC) AS [Position],
RANK() OVER( PARTITION BY [LeagueID] ORDER BY [TotalPointsLastRace] DESC, [TotalBonusPointsLastRace] DESC) AS [PositionLastRace],
ROW_NUMBER() OVER (PARTITION BY [LeagueID]
ORDER BY [TotalPoints] DESC, [TotalBonusPoints] DESC
) as [Position_Rownum]
FROM [DATA] ) AS d
INNER JOIN [Meeting] WITH (NOLOCK) ON [d].[MeetingID] = [Meeting].[MeetingID]
INNER JOIN [Course] ON [Meeting].[CourseID] = [Course].[CourseID]
OUTER APPLY (SELECT * FROM [dbo].[GetLeaguePrizes](d.[LeagueID])) t
WHERE (
([LeagueSizeID] = 3 AND [Position_Rownum] <= 50)
OR (d.[UserID] = @UserID AND [LeagueSizeID] = 3)
)
OR
(
[LeagueSizeID] in (1,2)
)
ORDER BY [LeagueID], [Position]
Any direction would be appreciated.
CROSS APPLY in SQL ServerCROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. It other words, result of CROSS APPLY doesn't contain any row of left side table expression for which no result is obtained from right side table expression.
The CROSS APPLY operator is semantically similar to INNER JOIN. It retrieves all the records from the table where there are corresponding matching rows in the output returned by the table valued function.
Thus, the CROSS APPLY is similar to an INNER JOIN, or, more precisely, like a CROSS JOIN with a correlated sub-query with an implicit join condition of 1=1. The OUTER APPLY operator returns all the rows from the left table expression regardless of its match with the right table expression.
You need to use OUTER APPLY (a mix of CROSS APPLY and LEFT JOIN).
SELECT * FROM [League]
INNER JOIN [League_Entry] ON [League].[LeagueID] = [League_Entry].[LeagueID]
OUTER APPLY [dbo].[GetPrizesForLeague]([League].[LeagueID]) t
Performance is very good with CROSS APPLY/OUTER APPLY. It's great for replacing some inner queries and cursors.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With