Alex Rivera | Logout

SQL returns less results when using UNION?

Asked 2011-09-02T18:49:27.230
43

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 [F
Edit
Report

1 Answer

111

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.

answered 2011-09-02T18:51:36.280

Your Answer