SQL returns less results when using UNION?

Question

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.

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.

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] >= convert(varchar(25),'2011-8-31',101)     and b1.[DateSubmitted] <= 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] >= convert(varchar(25),'2011-8-31',101)     and p.[DateSubmitted] <= convert(varchar(25),'2011-9-22',101)) order by     [Last Name] 

Why does it do this, and how can I fix it? I've tried adding a distinct to the [cptcode] area, but it of course generates an error.

Thanks for any help you can provide!

Answer 1

UNION will eliminate duplicate rows, whether they come between the two result sets or, as in your case, within just one result set. Try using UNION ALL instead.