SQL Server 2005's Correlated Joins
Read an article on an interesting new feature in SQL Server 2005, correlated joins using the APPLY statement.
In SQL Server 2000, you can't do something like this:
INNER JOIN
(SELECT TOP 3 * FROM LVCall WHERE LVTransactionID = lvt.ID ORDER BY CreateDate DESC) lvc on lvc.LVTransactionID = lvt.ID
(SELECT TOP 3 * FROM LVCall WHERE LVTransactionID = lvt.ID ORDER BY CreateDate DESC) lvc on lvc.LVTransactionID = lvt.ID
But, in 2005 you can do this
FROM
LVTransaction lvt
OUTER APPLY
(
SELECT top 3 *
FROM LVCall
WHERE lvtransactionID = lvt.ID
Order By CreateDate DESC
) as lvc
LVTransaction lvt
OUTER APPLY
(
SELECT top 3 *
FROM LVCall
WHERE lvtransactionID = lvt.ID
Order By CreateDate DESC
) as lvc
