T-sql 2012 prefer not use union all

You cannot vote on your own post
0

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?

why not:

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 
       LEFT JOIN o.dbo.custc2 
         ON c2.personid = c1.personid 
            AND c2.date = c1.date 
            AND c2.attributeid = 997 
WHERE  c1.attributeid = 1452
1 Like

The second select in the UNION ALL references a third table. I don't see how you could get around the UNION ALL. The larger question, for me, is WHY this is an issue for you?

I think this should be read:

from O.dbo.Cust C3

so it's the same as

from O.dbo.Cust C1
JOIN O.dbo.Cust c2

in the first query. At least, that's how I read it

2 Likes