title: SQL returns less results when using UNION?
body: <p>I have a SQL Server stored procedure that doesn't give me all the results when I add in the union and the second half. The first half will give me all 6 results, using the union, I only get 5.  </p>
  
  <p>There are 3 patients with their own [chart number], each have 2 items that should be displayed. if the [CPTCode] is the same for both entries of a patient, only one of the two entries show up when i add the union (it gives me all 6 with the same [cptcode] without the union). The second half isn't actually pulling any information with what i'm doing right now, but it is needed.</p>
  
  <pre><code>select      /*Patients with chart numbers*/
     B1.[chart Number],
     '0' as newPatient, 
     isnull(Lytec.[Last Name],'') as [Last Name], 
     isnull(Lytec.[First Name],'') as [First Name],
     isnull(Address.[Name],
     Lytec.[Facility Code]) as [Facility], 
     isnull(B1.DOS,'') as [DOS], 
     isnull(Ins.[Name],
     Lytec.[Primary Code]) as [Primary Code],
     isnull(B1.[CPTCode],'') as cptCode, 
     isnull(B1.[Units],'') as [Units], 
     isnull(B1.[Modifiers],'') as [Modifiers], 
     isnull(B1.[cptCodeOther],'') as [cptCodeOther],
     isnull(B1.[cptCode2],'') as cptCode2, 
     isnull(B1.[Units],'') as [Units2], 
     isnull(B1.[Modifiers2],'') as [Modifiers2], 
     isnull(B1.[cptCodeOther2],'') as [cptCodeOther2],
     'name' as ProviderName
  from 
     [sandboxmr].dbo.patient Lytec
     left join [dbo].[Billing] B1 on B1.[Chart Number] = Lytec.[Chart Number]
     left join [sandboxmr].dbo.[Address] Address on Lytec.[Facility Code] = Address.[Code]
     left join [sandboxmr].dbo.[Insurance] Ins on Lytec.[Primary Code] = Ins.[Code]
  where 
     b1.[userid] = 54
     and (b1.[DateSubmitted] &gt;= convert(varchar(25),'2011-8-31',101) 
     and b1.[DateSubmitted] &lt;= convert(varchar(25),'2011-9-22',101))
  
  union
  
  select      /*Patients without chart numbers*/
     cast(P.id as varchar(15)) as [chart number], 
     '1' as newPatient,  
     isnull(P.[Last Name],'') as [Last Name], 
     isnull(P.[First Name],'') as [First Name],
     isnull(Address.[Name],
     P.[Facility Code]) as [Facility], 
     isnull(IV.DOS,isnull(SV.DOS,'')) as [DOS], 
     isnull(Ins.[Name],P.[Primary_Code]) as [Primary Code],
     isnull(IV.[cptCode],isnull(SV.[cptCode],'')) as cptCode, 
     isnull(IV.[Units],isnull(SV.[Units],'')) as [Units], 
     isnull(IV.[Modifiers],isnull(SV.[Modifiers],'')) as [Modifiers],    
     isnull(IV.[cptcodeother],isnull(SV.[cptcodeother],'')) as [cptCodeOther],
     isnull(IV.[cptCode2],isnull(SV.[cptCode2],'')) as cptCode2, 
     isnull(IV.Units2,isnull(SV.Units2,'')) as [Units2], 
     isnull(IV.[Modifiers2],isnull(SV.[Modifiers2],'')) as [Modifiers2], 
     isnull(IV.[cptCodeOther2],isnull(SV.[cptCodeOther2],'')) as [cptCodeOther2],
     'Name' as ProviderName
  from 
     [DNSList].[dbo].[Patient] P 
     left join [dbo].[InitialVisits] IV on p.emr_id = IV.patientid
     left join [dbo].[SubsequentVisits] SV on p.emr_id = SV.patientid
     left join [sandboxmr].dbo.[Address] Address on P.[Facility Code] = Address.[Code]
  left join [sandboxmr].dbo.[Insurance] Ins on P.[Primary_Code] = Ins.[Code]
  where 
     p.[userid] = 54 
     and p.[Chart Number] is null
     and (p.[DateSubmitted] &gt;= convert(varchar(25),'2011-8-31',101) 
     and p.[DateSubmitted] &lt;= convert(varchar(25),'2011-9-22',101))
  order by 
     [Last Name]
  </code></pre>
  
  <p>Why does it do this, and how can I fix it? I've tried adding a <code>distinct</code> to the [cptcode] area, but it of course generates an error.</p>
  
  <p>Thanks for any help you can provide!</p>