Subquery Help Again :(

I am getting results back from my Subquery, however they are not the right facility on the Patient Visit. Thoughts?


My full query:

DECLARE @pPatientVisitID INT
SELECT @pPatientVisitID = pv.PatientVisitID
FROM PatientVisit pv

SELECT cIPV.ImmunCode,
cIPV.ImmunDescription,
cIPV.Category,
pp.Last,
pp.First,
ISNULL(pp.Middle,'') AS Middle,
cIPV.ImmunDte,
cIPV.ListOrder,
cIPV.Last + ', ' + cIPV.First + case when cIPV.middle is null then + '' else ' ' + cIPV.middle end as Name,
dbo.cusCalcAgeYearsMonths(AgeYears,AgeMonths) AS AgeAtImmun,
pp.PatientID AS MedicalRecordNumber,
(SELECT fac.ListName
FROM cusvImmunPatVisits cIPV
INNER JOIN PatientVisit pv ON cIPV.PatientVisitID = pv.PatientVisitID
INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId
WHERE cIPV.PatientvisitId = @pPatientVisitID
) AS Facility

FROM cusvImmunPatVisits cIPV
INNER JOIN PatientProfile pp ON cIPV.PatientProfileID = pp.PatientProfileID

WHERE cIPV.ImmunDte >= ISNULL(NULL,'1/1/1900') AND cIPV.ImmunDte < dateadd(day,1,ISNULL(NULL,'1/1/3000'))
AND --Filter on patient
(
(NULL IS NOT NULL AND pp.PatientProfileID IN (NULL)) OR
(NULL IS NULL)
)