SQL trick with JOIN vs APPLY (JOINING to get the top 1)

So, assume this:

select * from meddenials

DenialId DenialTypeId DenialCode DenialDescrip
1                       1 1420         Not Medically Necessary
2                       1 1500         Other transportation available
3                       1 1600         No Eligibility Coverage on date of service
4                       2 100                 Missing Signature
5                       2 200                 Incomplete Information
6                       2 300                 Mileage over estimate threshold

And


select * from meddenialtypes



DenialTypeId DenialTypeDescrip
1                 Trip Denial
2                 Claim Denial


And

select * from medreimbursementdenials where bookingid = 44479


BookingId DenialTypeId DenialId
44479                       1 1
44479                       2 5


So, for an Encounter file, we wanted to get the top denial reason, so I tried something like this:



SELECT DenialReason.*,md.*
FROM Booking b
LEFT OUTER JOIN ( SELECT TOP 1 DenialId, BookingId FROM MedReimbursementDenials where bookingid = b.bookingid )
AS DenialReason
LEFT OUTER JOIN MedDenials md on md.DenialId = DenialReason.DenialId
where b.bookingid = 44479

but that causes an error:

Msg 156, Level 15, State 1, Line 6

Incorrect syntax near the keyword 'where'.

So I tried moving the where clause out of the "sub table":

SELECT DenialReason.*,md.* 

FROM Booking b

LEFT OUTER JOIN ( SELECT TOP 1 DenialId, BookingId FROM MedReimbursementDenials  ) 

AS DenialReason on DenialReason.BookingId = b.BookingId

LEFT OUTER JOIN MedDenials md on md.DenialId = DenialReason.DenialId 

where b.bookingid = 44479

Which is ok syntactically, but doesn't return the results the way we want:

DenialId BookingId DenialId DenialTypeId DenialCode DenialDescrip

NULL NULL NULL NULL NULL NULL

So, I googled around and found this page:

http://stackoverflow.com/questions/2033699/top-1-with-a-left-join

which introduced a key word I wasn't familiar with "OUTER APPLY"

SELECT DenialReason.*,md.* 

FROM Booking b

OUTER APPLY ( SELECT TOP 1 DenialId, BookingId FROM MedReimbursementDenials where bookingid = b.bookingid ) 

AS DenialReason 

LEFT OUTER JOIN MedDenials md on md.DenialId = DenialReason.DenialId 

where b.bookingid = 44479

does the trick:

DenialId BookingId DenialId DenialTypeId DenialCode DenialDescrip

1               44479 1                       1 1420 Not Medically Necessary