t-sql 2012 prefer to not use union all

• dianerstein 8713

  SSCrazy

  Points: 2197

  More actions

  January 26, 2017 at 1:13 pm

  #339733

  

  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?

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 26, 2017 at 1:26 pm

  #1925190

  I'm assuming that it's only one table with 4 different alias. It could be handled by a left join.
  
Select c1.personID
  ,c1.enrollmentID
  ,attributeID=case when c2.value = 'N' then 3371 else 3370 end
  ,c1.value
  ,c1.date
  ,c1.customGUID
  ,c1.districtID
  ,ISNULL( c2.value, '') AS value
from O.dbo.Cust C1
LEFT
JOIN O.dbo.Cust C2 on c2.personID=c1.personID
      and c2.date = c1.date
      and C2.attributeID= 997
where C1.attributeID = 1452


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2