In t-sql 2012, the following sql works when I use a union all statement. However I would prefer not to
use a union all if at all possible. The problem is there are a few times when the join does not find anythning.
When the join occurs there are some cases where attributeID= 997 or 1452, does not have a match by personID.
The goal is is there is value <>'N' in all cases, the result attributeID value should be set to 3370.
Select c1.personID
,c1.enrollmentID
,attributeID=case when c2.value = 'N' then 3371 else 3370 end
,c1.value
,c1.date
,c1.customGUID
,c1.districtID
,c2.value
from O.dbo.CustC1
JOIN O.dbo.Custc2
on c2.personID=c1.personID
and c2.date = c1.date
and C2.attributeID= 997
where C1.attributeID = 1452
UNION ALL
Select c3.personID
,c3.enrollmentID
,attributeID=3370
,c3.value
,c3.date
,c3.customGUID
,c3.districtID
,value=''
from O.dbo.CustC3
where C3.attributeID = 1452
and c3.personID not in
(select c4.personID from O.dbo.CustC4
where c4.attributeID= 997)
Thus can you modify the sql above to meet my requirement?