title: SQL Query Returning Duplicate values after using UNION and Distinct
body: <p>I have a SQL query where I am trying to get distinct values after making union of two queries. I have already used Distinct in both of the queries. </p>
  
  <p>I have <code>RegID</code> as primary key in the <code>Registration</code> table and for each entry in registration table there are corresponding entries in <code>RoomType</code> i.e, <code>RoomType</code> table has <code>RegID</code> as foreign key. </p>
  
  <p>Now the problem is if have more than one entry in <code>RoomType</code> table for a particular <code>RegID</code> I am getting duplicate Regid's.</p>
  
  <p>My query print is like this:</p>
  
  <pre><code>SELECT DISTINCT 
      dbo.tbRegistration.RegID, 
      dbo.tbRegistration.PropertyName, dbo.tbRegistration.IsActive, 
      dbo.tbRegistration.Stars, dbo.tbRegistration.City, 
      dbo.tbRegistration.Address, 
      dbo.tbHotelImages.MainImage, dbo.tbHotelImages.Img1, 
      dbo.tbHotelImages.Img2, dbo.tbHotelImages.Img3, 
      dbo.tbHotelImages.Img4, dbo.tbHotelImages.Img5, 
      dbo.tbHotelImages.Img6, dbo.tbHotelImages.Img7, 
      dbo.tbHotelImages.Img8, dbo.tbHotelImages.Img9, 
      dbo.tbRegistration.PropertyID, 
      dbo.tbHotelFeatures.*, 
      dbo.tbBillingPlan.BaseRate, dbo.tbRoomType.Name as RoomTypeName
  FROM         
      dbo.tbRoomType 
  LEFT OUTER JOIN
      dbo.tbRegistration ON dbo.tbRoomType.RegID = dbo.tbRegistration.RegID  
  LEFT OUTER JOIN
      dbo.tbBillingPlan ON dbo.tbRoomType.RTID = dbo.tbBillingPlan.RTID    
  LEFT OUTER JOIN
      dbo.tbHotelFeatures ON dbo.tbRegistration.RegID = dbo.tbHotelFeatures.RegID  
  LEFT OUTER JOIN
      dbo.tbHotelImages ON dbo.tbRegistration.RegID = dbo.tbHotelImages.RegID                                          
  WHERE 
      tbRegistration.PMS = 0 
      AND tbRegistration.Stars IN (0) OR 1=1 
      AND (tbRegistration.City LIKE '%' + @SearchText + '%')  
      AND (tbRoomType.Adults &gt;= @Adults OR tbRoomType.Children &gt;= @Children)
      AND (dbo.GetFreeRoomType(dbo.tbRoomType.RTID, @CheckIn, @CheckOut) &gt; 0) 
      AND dbo.tbRegistration.IsActive = 1     
  
  UNION
  
  SELECT DISTINCT 
      dbo.tbRegistration.RegID  , dbo.tbRegistration.PropertyName, dbo.tbRegistration.IsActive, dbo.tbRegistration.Stars, dbo.tbRegistration.City, dbo.tbRegistration.Address, 
                        dbo.tbHotelImages.MainImage, dbo.tbHotelImages.Img1, dbo.tbHotelImages.Img2, dbo.tbHotelImages.Img3, dbo.tbHotelImages.Img4, dbo.tbHotelImages.Img5, 
                        dbo.tbHotelImages.Img6, dbo.tbHotelImages.Img7, dbo.tbHotelImages.Img8, dbo.tbHotelImages.Img9, dbo.tbRegistration.PropertyID    , 
                        dbo.tbHotelFeatures.*, dbo.tbBillingPlan.BaseRate,dbo.tbRoomType.Name as RoomTypeName
  FROM         dbo.tbRoomType left OUTER JOIN
                        dbo.tbRegistration ON dbo.tbRoomType.RegID = dbo.tbRegistration.RegID  left OUTER JOIN
                        dbo.tbBillingPlan ON dbo.tbRoomType.RTID = dbo.tbBillingPlan.RTID    LEFT OUTER JOIN
                        dbo.tbHotelFeatures ON dbo.tbRegistration.RegID = dbo.tbHotelFeatures.RegID  LEFT OUTER JOIN
                        dbo.tbHotelImages ON dbo.tbRegistration.RegID = dbo.tbHotelImages.RegID                                           
         Where tbRegistration.PMS=1  and   tbRegistration.Stars IN (   0 ) or 1=1 and    (tbRegistration.City like  '%' + @SearchText + '%' )    
  
  and (tbRoomType.Adults&gt;=@Adults  or tbRoomType.Children&gt;=@Children )
   and(dbTestSparrowPMS . dbo.GetFreeRoomType(dbo.tbRoomType.ID, @CheckIn, @CheckOut) &gt;0) and dbo.tbRegistration.IsActive=1
  
  
  
     order by  tbRegistration.RegID
  </code></pre>
  
  <p>and My Procedure is like this:</p>
  
  <pre><code>USE [dbTestSearchMyHotel]
  GO
  /****** Object:  StoredProcedure [dbo].[prRegistrationSearchFreeHotels]    Script Date: 04/01/2013 12:06:26 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  -- =============================================
  -- Author:      &lt;Author,,Name&gt;
  -- Create date: &lt;Create Date,,&gt;
  -- Description: &lt;Description,,&gt;
  -- =============================================
  ALTER PROCEDURE [dbo].[prRegistrationSearchFreeHotels]   -- 's','4/27/2013','4/28/2013',1,1,0,0,0,0,0,0,0
  
      -- Add the parameters for the stored procedure here select * from tbRegistration where Stars in(1,4)
  
  @SearchText varchar(4000),
  @CheckIn date,
  @CheckOut date,
  @Adults int,
  @Children int,
  @Min float,
  @Max float,
  @OneStar bit,
  @TwoStar bit,
  @ThreeStar bit,
  @FourStar bit,
  @FiveStar bit
  
      AS
       Declare @SQLQuery AS NVarchar(4000)
  Declare @ParamDefinition AS NVarchar(4000) 
  declare @n as bit
  set @n=0
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
      -- Insert statements for procedure here
  
    Set @SQLQuery='SELECT DISTINCT 
                        dbo.tbRegistration.RegID  , dbo.tbRegistration.PropertyName,dbo.tbRegistration.IsActive, dbo.tbRegistration.Stars, dbo.tbRegistration.City, dbo.tbRegistration.Address, 
                        dbo.tbHotelImages.MainImage, dbo.tbHotelImages.Img1, dbo.tbHotelImages.Img2, dbo.tbHotelImages.Img3, dbo.tbHotelImages.Img4, dbo.tbHotelImages.Img5, 
                        dbo.tbHotelImages.Img6, dbo.tbHotelImages.Img7, dbo.tbHotelImages.Img8, dbo.tbHotelImages.Img9, dbo.tbRegistration.PropertyID    , 
                        dbo.tbHotelFeatures.*, dbo.tbBillingPlan.BaseRate,dbo.tbRoomType.Name as RoomTypeName
  FROM         dbo.tbRoomType left OUTER JOIN
                        dbo.tbRegistration ON dbo.tbRoomType.RegID = dbo.tbRegistration.RegID  left OUTER JOIN
                        dbo.tbBillingPlan ON dbo.tbRoomType.RTID = dbo.tbBillingPlan.RTID    LEFT OUTER JOIN
                        dbo.tbHotelFeatures ON dbo.tbRegistration.RegID = dbo.tbHotelFeatures.RegID  LEFT OUTER JOIN
                        dbo.tbHotelImages ON dbo.tbRegistration.RegID = dbo.tbHotelImages.RegID                                          
                                Where  tbRegistration.PMS=0 and  tbRegistration.Stars IN (   ' 
       if @OneStar =1 
       begin
      set @n =1
      set @SQLQuery =@SQLQuery +'  1  '
      end
  
    if @TwoStar =1 
    begin
       if @n =1
       begin
       set @SQLQuery =@SQLQuery +' , 2 '
         end
       else
       begin
       set @n=1
       set @SQLQuery =@SQLQuery +' 2 '
        end
   end
  
    if @ThreeStar =1 
    begin
       if @n =1
       begin
      set @SQLQuery =@SQLQuery +' , 3 '
      end
      else
       begin
       set @n=1
       set @SQLQuery =@SQLQuery +' 3  '
      end
   end
  
    if @FourStar =1 
    begin
       if @n =1
       begin
      set @SQLQuery =@SQLQuery +' , 4 '
      end
      else
      begin
       set @n=1
        set @SQLQuery =@SQLQuery +' 4 '
      end
      end
  
    if @FiveStar =1 
    begin
      if @n =1
       begin
       set @SQLQuery =@SQLQuery +' , 5 '
      end
       else
       begin
       set @n=1
       set @SQLQuery =@SQLQuery +' 5 '
      end
      end                         
     if @n=0
   begin
    set @SQLQuery =@SQLQuery +' 0 ) or 1=1 and '
   end      
   else
   begin                      
   set @SQLQuery =@SQLQuery +' )    and ' 
   end
   set @SQLQuery =@SQLQuery +'    (tbRegistration.City like  ''%'' + @SearchText + ''%'' )  
  
   and (tbRoomType.Adults&gt;=@Adults  or tbRoomType.Children&gt;=@Children )
   and( dbo.GetFreeRoomType(dbo.tbRoomType.RTID, @CheckIn, @CheckOut) &gt;0) and dbo.tbRegistration.IsActive=1    '  
   if @Min &gt; 0 and @Max &gt; 0   
   set @SQLQuery =@SQLQuery +' and (tbBillingPlan.BaseRate&gt;=@Min) and  (tbBillingPlan.BaseRate&lt;=@Max)  '
  
  
  
  
  
    set @SQLQuery =@SQLQuery +' union  '
    set @n=0
  Set @SQLQuery=@SQLQuery+'SELECT DISTINCT 
                        dbo.tbRegistration.RegID  , dbo.tbRegistration.PropertyName, dbo.tbRegistration.IsActive, dbo.tbRegistration.Stars, dbo.tbRegistration.City, dbo.tbRegistration.Address, 
                        dbo.tbHotelImages.MainImage, dbo.tbHotelImages.Img1, dbo.tbHotelImages.Img2, dbo.tbHotelImages.Img3, dbo.tbHotelImages.Img4, dbo.tbHotelImages.Img5, 
                        dbo.tbHotelImages.Img6, dbo.tbHotelImages.Img7, dbo.tbHotelImages.Img8, dbo.tbHotelImages.Img9, dbo.tbRegistration.PropertyID    , 
                        dbo.tbHotelFeatures.*, dbo.tbBillingPlan.BaseRate,dbo.tbRoomType.Name as RoomTypeName
  FROM         dbo.tbRoomType left OUTER JOIN
                        dbo.tbRegistration ON dbo.tbRoomType.RegID = dbo.tbRegistration.RegID  left OUTER JOIN
                        dbo.tbBillingPlan ON dbo.tbRoomType.RTID = dbo.tbBillingPlan.RTID    LEFT OUTER JOIN
                        dbo.tbHotelFeatures ON dbo.tbRegistration.RegID = dbo.tbHotelFeatures.RegID  LEFT OUTER JOIN
                        dbo.tbHotelImages ON dbo.tbRegistration.RegID = dbo.tbHotelImages.RegID                                           
         Where tbRegistration.PMS=1  and   tbRegistration.Stars IN (  ' 
       if @OneStar =1 
       begin
      set @n =1
   set @SQLQuery =@SQLQuery +'  1  '
   end
       if @TwoStar =1 
       begin
           if @n =1
          begin
          set @SQLQuery =@SQLQuery +', 2 '
          end
          else
          begin
           set @SQLQuery =@SQLQuery +' 2 '
           set @n=1
              end
       end
    if @ThreeStar =1 
    begin
           if @n =1
            begin
          set @SQLQuery =@SQLQuery +' , 3 '
           end
          else
           begin
           set @n=1
          set @SQLQuery =@SQLQuery +' 3'
              end
   end
  
    if @FourStar =1 
    begin
           if @n =1
           begin
          set @SQLQuery =@SQLQuery +' , 4 '
          end
          else
          begin
          Set @n=1
          set @SQLQuery =@SQLQuery +' 4 '
          end
   end
     if @FiveStar =1 
     begin
           if @n =1
           begin
          set @SQLQuery =@SQLQuery +', 5 '
          end
          else
          begin
          Set @n=1
           set @SQLQuery =@SQLQuery +' 5 '
          end
   end                         
    if @n=0
   begin
    set @SQLQuery =@SQLQuery +' 0 ) or 1=1 and '
   end      
   else
   begin                      
   set @SQLQuery =@SQLQuery +' )    and ' 
   end
   set @SQLQuery =@SQLQuery +'   (tbRegistration.City like  ''%'' + @SearchText + ''%'' )    
  
  and (tbRoomType.Adults&gt;=@Adults  or tbRoomType.Children&gt;=@Children )
   and(dbTestSparrowPMS . dbo.GetFreeRoomType(dbo.tbRoomType.ID, @CheckIn, @CheckOut) &gt;0) and dbo.tbRegistration.IsActive=1
  
  
  
    '  
  
   if @Min &gt; 0 and @Max &gt; 0   
   set @SQLQuery =@SQLQuery +' and (tbBillingPlan.BaseRate&gt;=@Min) and  (tbBillingPlan.BaseRate&lt;=@Max) '
  
  
  
   set @SQLQuery=@SQLQuery + ' order by  tbRegistration.RegID'
    --
  
         Set @ParamDefinition = ' 
  @SearchText varchar(4000),
  @CheckIn date,
  @CheckOut date,
  @Adults int,
  @Children int,
  @Min float,
  @Max float,
  @OneStar bit,
  @TwoStar bit,
  @ThreeStar bit,
  @FourStar bit,
  @FiveStar bit'
     --print @SQLQuery           
      /* Execute the Transact-SQL String with all parameter value's 
         Using sp_executesql Command */
      Execute sp_Executesql     @SQLQuery, 
                  @ParamDefinition, 
  
                 @SearchText ,
                 @CheckIn ,
                 @CheckOut ,
                 @Adults ,
                 @Children ,
                 @Min,
                 @Max,
                 @OneStar ,
                 @TwoStar ,
                 @ThreeStar ,
                 @FourStar ,
                 @FiveStar 
  
  
  
      return
  
  
  
    end
  </code></pre>