SQL Server: LEFT OUTER JOIN with TOP 1 to Select at Most One Row

Question

I basically need to do a left outer join on 2 tables (CarePlan and Referrals) problem is i need the newest Referral If it exists, its ok if it doesnt.

I have these 2 queries 1. joins the CarePlan/Referral tables - creates duplicate careplans if there are multiple referrals for a careplan, or has no referral information at all (left outer join) 2. select the top 1 Referral based on Date, given a CarePlanId

I'd like to combine these 2 so i grab all the careplans and its referrals if it exists, if it does - take only the newest referral

select * from CarePlan c //query 1
left outer join Referral r on 
r.CarePlanId = c.CarePlanId


select top 1 * from Referral r //query 2
where r.CarePlanId = '1'
order by ReferralDate desc

EDIT:

The first query gives me something like this:

CarePlanID    ReferralId     ReferralDate
----------    ----------     ------------
1             1              05/15/12
2             NULL           NULL
1             2              05/10/12  //Old date, dont want this careplan

The second query will give me the referral with the newest date

ReferralId    ReferralDate
----------    ------------
1             05/15/12

The Referral data, may have 0 or more referrals belonging to a Careplan

ReferralID  CarePlanId    Date
----------  ----------    ----
1           1             05/15/12
2           1             05/10/12

Ultimately I want a query that gives me careplans with referrals that have the newest date, or null for referrals if it doesnt have it

like this:

CarePlanId   ReferralId    ReferralDate
----------   ----------    ------------
1            1             05/15/12
2            NULL          NULL

Thanks - i hope this makes sense

Answer 1

select *
from CarePlan c
outer apply (
    select top 1 * --top N rows
    from Referral r
    where r.CarePlanId = c.CarePlanId --join condition
    order by /*fill this in!*/
) x

Be aware that this forces a loop join due to optimizer weaknesses up to and including version 2014.