SQLTeam.com | Weblogs | Forums

T-sql 2012 prefer not use union all


You cannot vote on your own post

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
,attributeID=case when c2.value = 'N' then 3371 else 3370 end
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
Select c3.personID
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, 
                     WHEN c2.value = 'N' THEN 3371 
                     ELSE 3370 
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


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