asked on

t-sql union not removing duplicate records

Hi,

Can anyone identify why my union statements are not removing duplicates - they are returning the same thing and I am using an order by statement at end.
Would love an expert to provide me with more information on how wildcard searches like '%test%' are returned - do they return all records beginning with 'test' THEN containing 'test' THEN ending with 'test'.

Cheers.
Steve

--region [dbo].[SelectPagedSiteSearch]

------------------------------------------------------------------------------------------------------------------------
-- Procedure Name: [dbo].[SelectPagedSiteSearch]
-- Author:         Stephen McCormack - stephenm@mwebsolutions.com.au
-- Company:        MWeb Solutions Pty Ltd
--------- --------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[SelectPagedSiteSearch]

	-- paging parameters
	@PageNumber int = 1,
	@PageSize int = 10,
	@SearchText varchar(50)

AS

	-- dont return number of records affected
	SET NOCOUNT ON
			
	--isolation level
	SET TRANSACTION ISOLATION LEVEL READ COMMITTED

	-- parameter validation
	IF (@PageNumber < 1)	SET @PageNumber = 1
	IF (@PageSize < 1)		SET @PageSize = 10

	-- system function stores
	DECLARE 
		@ErrStatus int, 	-- stores error status
		@RowsAffected int		-- stores number of rows affected

	----------------------------------------------------
	-- Create like text.
	----------------------------------------------------
	DECLARE 
		@StartsWithText varchar(60),
		@LikeText varchar(60),
		@EndsWithText varchar(60)

	SELECT
		@StartsWithText = LOWER(@SearchText) + '%',
		@LikeText = '%' +LOWER(@SearchText) + '%',
		@EndsWithText = '%' +LOWER(@SearchText)

	----------------------------------------------------
	-- Find out where we will start our records from
	----------------------------------------------------
	DECLARE @RecCount int
	SELECT @RecCount = @PageSize * @PageNumber + 1
 
	----------------------------------------------------
	-- Find out the first and last record we want
	----------------------------------------------------
	DECLARE @FirstRec int, @LastRec int
	SELECT @FirstRec = (@PageNumber - 1) * @PageSize
	SELECT @LastRec = (@PageNumber * @PageSize + 1)



	----------------------------------------------------
	-- Create a temporary table to hold the results
	----------------------------------------------------
	DECLARE @SearchResultsTable TABLE 
	(
		[ID] int IDENTITY(1,1),
		[PageTitle] varchar(128),
		[PageUrl] varchar(128),
		[IsNewWin] bit,
		[ShortDescription] varchar(512),
		[MediaUrl] varchar(128) NULL
	)


		
	----------------------------------------------------
	-- Search Products
	----------------------------------------------------
	INSERT INTO @SearchResultsTable 
	(
		[PageTitle],
		[PageUrl],
		[IsNewWin],
		[ShortDescription],
		[MediaUrl]
	)
	SELECT
		
		[dbo].[Products].[ProductTitle],
		'/products/productDetail.aspx?id=' + CAST([dbo].[Products].[ProductID] AS varchar),
		0,
		SUBSTRING([dbo].[Products].[ShortDescription], 1, 512),
		[dbo].[ProductMedia].[MediaUrl]
	FROM
		[dbo].[Products]
		INNER JOIN [dbo].[Suppliers] ON [dbo].[Products].[SupplierID] = [dbo].[Suppliers].[SupplierID]
		INNER JOIN [dbo].[ProductCategories] ON [dbo].[Products].[ProductCategoryID] = [dbo].[ProductCategories].[ProductCategoryID]
		LEFT OUTER JOIN [dbo].[Brands] ON [dbo].[Products].[BrandID] = [dbo].[Brands].[BrandID]
		LEFT OUTER JOIN [dbo].[ProductMedia] ON [dbo].[ProductMedia].[ProductID] = [dbo].[Products].[ProductID]
		INNER JOIN [dbo].[MediaTypes] ON [dbo].[ProductMedia].[MediaTypeID] = [dbo].[MediaTypes].[MediaTypeID]
	WHERE
		--[dbo].[Products].[IsFeatured] = 1 AND
		[dbo].[Products].[IsDiscontinued] = 0 AND
		(
			LOWER([dbo].[Products].[ProductTitle]) LIKE @StartsWithText
		)
	UNION
	SELECT
		[dbo].[Products].[ProductTitle],
		'/products/productDetail.aspx?id=' + CAST([dbo].[Products].[ProductID] AS varchar),
		0,
		SUBSTRING([dbo].[Products].[ShortDescription], 1, 512),
		[dbo].[ProductMedia].[MediaUrl]
	FROM
		[dbo].[Products]
		INNER JOIN [dbo].[Suppliers] ON [dbo].[Products].[SupplierID] = [dbo].[Suppliers].[SupplierID]
		INNER JOIN [dbo].[ProductCategories] ON [dbo].[Products].[ProductCategoryID] = [dbo].[ProductCategories].[ProductCategoryID]
		LEFT OUTER JOIN [dbo].[Brands] ON [dbo].[Products].[BrandID] = [dbo].[Brands].[BrandID]
		LEFT OUTER JOIN [dbo].[ProductMedia] ON [dbo].[ProductMedia].[ProductID] = [dbo].[Products].[ProductID]
		INNER JOIN [dbo].[MediaTypes] ON [dbo].[ProductMedia].[MediaTypeID] = [dbo].[MediaTypes].[MediaTypeID]
	WHERE
		--[dbo].[Products].[IsFeatured] = 1 AND
		[dbo].[Products].[IsDiscontinued] = 0 AND
		(
			LOWER([dbo].[Products].[ProductTitle]) LIKE @LikeText
		)
	UNION
	SELECT
		[dbo].[Products].[ProductTitle],
		'/products/productDetail.aspx?id=' + CAST([dbo].[Products].[ProductID] AS varchar),
		0,
		SUBSTRING([dbo].[Products].[ShortDescription], 1, 512),
		[dbo].[ProductMedia].[MediaUrl]
	FROM
		[dbo].[Products]
		INNER JOIN [dbo].[Suppliers] ON [dbo].[Products].[SupplierID] = [dbo].[Suppliers].[SupplierID]
		INNER JOIN [dbo].[ProductCategories] ON [dbo].[Products].[ProductCategoryID] = [dbo].[ProductCategories].[ProductCategoryID]
		LEFT OUTER JOIN [dbo].[Brands] ON [dbo].[Products].[BrandID] = [dbo].[Brands].[BrandID]
		LEFT OUTER JOIN [dbo].[ProductMedia] ON [dbo].[ProductMedia].[ProductID] = [dbo].[Products].[ProductID]
		INNER JOIN [dbo].[MediaTypes] ON [dbo].[ProductMedia].[MediaTypeID] = [dbo].[MediaTypes].[MediaTypeID]
	WHERE
		--[dbo].[Products].[IsFeatured] = 1 AND
		[dbo].[Products].[IsDiscontinued] = 0 AND
		(
			LOWER([dbo].[Products].[ProductTitle]) LIKE @EndsWithText
		)
	UNION
	SELECT
		[dbo].[Products].[ProductTitle],
		'/products/productDetail.aspx?id=' + CAST([dbo].[Products].[ProductID] AS varchar),
		0,
		SUBSTRING([dbo].[Products].[ShortDescription], 1, 512),
		[dbo].[ProductMedia].[MediaUrl]
	FROM
		[dbo].[Products]
		INNER JOIN [dbo].[Suppliers] ON [dbo].[Products].[SupplierID] = [dbo].[Suppliers].[SupplierID]
		INNER JOIN [dbo].[ProductCategories] ON [dbo].[Products].[ProductCategoryID] = [dbo].[ProductCategories].[ProductCategoryID]
		LEFT OUTER JOIN [dbo].[Brands] ON [dbo].[Products].[BrandID] = [dbo].[Brands].[BrandID]
		LEFT OUTER JOIN [dbo].[ProductMedia] ON [dbo].[ProductMedia].[ProductID] = [dbo].[Products].[ProductID]
		INNER JOIN [dbo].[MediaTypes] ON [dbo].[ProductMedia].[MediaTypeID] = [dbo].[MediaTypes].[MediaTypeID]
	WHERE
		--[dbo].[Products].[IsFeatured] = 1 AND
		[dbo].[Products].[IsDiscontinued] = 0 AND
		(
			LOWER([dbo].[Products].[ProductCategoryID]) LIKE @StartsWithText OR
			LOWER([dbo].[Suppliers].[SupplierName]) LIKE @StartsWithText OR  /* fk name column */
			LOWER([dbo].[Brands].[BrandName]) LIKE @StartsWithText OR  /* fk name column */
			--LOWER([dbo].[Products].[ProductTitle]) LIKE @StartsWithText OR
			LOWER([dbo].[Products].[TagLine]) LIKE @StartsWithText OR
			LOWER([dbo].[Products].[ShortDescription]) LIKE @StartsWithText OR
			LOWER([dbo].[Products].[HtmlDescription]) LIKE @StartsWithText OR
			LOWER([dbo].[Products].[IdealNotes]) LIKE @StartsWithText OR
			LOWER([dbo].[Products].[ComplianceNotes]) LIKE @StartsWithText OR
			LOWER([dbo].[Products].[ProductCode]) LIKE @StartsWithText
		)
	UNION
	SELECT
		[dbo].[Products].[ProductTitle],
		'/products/productDetail.aspx?id=' + CAST([dbo].[Products].[ProductID] AS varchar),
		0,
		SUBSTRING([dbo].[Products].[ShortDescription], 1, 512),
		[dbo].[ProductMedia].[MediaUrl]
	FROM
		[dbo].[Products]
		INNER JOIN [dbo].[Suppliers] ON [dbo].[Products].[SupplierID] = [dbo].[Suppliers].[SupplierID]
		INNER JOIN [dbo].[ProductCategories] ON [dbo].[Products].[ProductCategoryID] = [dbo].[ProductCategories].[ProductCategoryID]
		LEFT OUTER JOIN [dbo].[Brands] ON [dbo].[Products].[BrandID] = [dbo].[Brands].[BrandID]
		LEFT OUTER JOIN [dbo].[ProductMedia] ON [dbo].[ProductMedia].[ProductID] = [dbo].[Products].[ProductID]
		INNER JOIN [dbo].[MediaTypes] ON [dbo].[ProductMedia].[MediaTypeID] = [dbo].[MediaTypes].[MediaTypeID]
	WHERE
		--[dbo].[Products].[IsFeatured] = 1 AND
		[dbo].[Products].[IsDiscontinued] = 0 AND
		(
			LOWER([dbo].[Products].[ProductCategoryID]) LIKE @LikeText OR
			LOWER([dbo].[Suppliers].[SupplierName]) LIKE @LikeText OR  /* fk name column */
			LOWER([dbo].[Brands].[BrandName]) LIKE @LikeText OR  /* fk name column */
			--LOWER([dbo].[Products].[ProductTitle]) LIKE @LikeText OR
			LOWER([dbo].[Products].[TagLine]) LIKE @LikeText OR
			LOWER([dbo].[Products].[ShortDescription]) LIKE @LikeText OR
			LOWER([dbo].[Products].[HtmlDescription]) LIKE @LikeText OR
			LOWER([dbo].[Products].[IdealNotes]) LIKE @LikeText OR
			LOWER([dbo].[Products].[ComplianceNotes]) LIKE @LikeText OR
			LOWER([dbo].[Products].[ProductCode]) LIKE @LikeText
		)
	UNION
	SELECT
		[dbo].[Products].[ProductTitle],
		'/products/productDetail.aspx?id=' + CAST([dbo].[Products].[ProductID] AS varchar),
		0,
		SUBSTRING([dbo].[Products].[ShortDescription], 1, 512),
		[dbo].[ProductMedia].[MediaUrl]
	FROM
		[dbo].[Products]
		INNER JOIN [dbo].[Suppliers] ON [dbo].[Products].[SupplierID] = [dbo].[Suppliers].[SupplierID]
		INNER JOIN [dbo].[ProductCategories] ON [dbo].[Products].[ProductCategoryID] = [dbo].[ProductCategories].[ProductCategoryID]
		LEFT OUTER JOIN [dbo].[Brands] ON [dbo].[Products].[BrandID] = [dbo].[Brands].[BrandID]
		LEFT OUTER JOIN [dbo].[ProductMedia] ON [dbo].[ProductMedia].[ProductID] = [dbo].[Products].[ProductID]
		INNER JOIN [dbo].[MediaTypes] ON [dbo].[ProductMedia].[MediaTypeID] = [dbo].[MediaTypes].[MediaTypeID]
	WHERE
		--[dbo].[Products].[IsFeatured] = 1 AND
		[dbo].[Products].[IsDiscontinued] = 0 AND
		(
			LOWER([dbo].[Products].[ProductCategoryID]) LIKE @EndsWithText OR
			LOWER([dbo].[Suppliers].[SupplierName]) LIKE @EndsWithText OR  /* fk name column */
			LOWER([dbo].[Brands].[BrandName]) LIKE @EndsWithText OR  /* fk name column */
			--LOWER([dbo].[Products].[ProductTitle]) LIKE @EndsWithText OR
			LOWER([dbo].[Products].[TagLine]) LIKE @EndsWithText OR
			LOWER([dbo].[Products].[ShortDescription]) LIKE @EndsWithText OR
			LOWER([dbo].[Products].[HtmlDescription]) LIKE @EndsWithText OR
			LOWER([dbo].[Products].[IdealNotes]) LIKE @EndsWithText OR
			LOWER([dbo].[Products].[ComplianceNotes]) LIKE @EndsWithText OR
			LOWER([dbo].[Products].[ProductCode]) LIKE @EndsWithText
		)
	ORDER BY --union needs order statement to remove duplicates
		[dbo].[Products].[ProductTitle] ASC
		


	----------------------------------------------------
	-- Return paged set.
	----------------------------------------------------  	
	SELECT
		[ID],
		[PageTitle],
		[PageUrl],
		[IsNewWin],
		[ShortDescription],
		[MediaUrl]
	FROM
		@SearchResultsTable
	WHERE 
		[ID] > @FirstRec 
		AND [ID] < @LastRec
	ORDER BY 
		[ID] ASC
		
	/* must use single statement immediately to store system functions 
	as all DML statements, SELECT, IF, PRINT and SET will reset @@error to zero */
	SELECT 
		@ErrStatus = @@ERROR

	-- check for errors
	IF @ErrStatus <> 0
	BEGIN
		RAISERROR( 'Error occurred in stored procedure ''[dbo].[SelectPagedSiteSearch]''', 10, 1 )
		RETURN @ErrStatus
	END


	-- turn back on the number of rows affected
	SET NOCOUNT OFF

	-- return success
	RETURN 0


--endregion

Open in new window

cyberkiwi

[how wildcard searches like '%test%' are returned - do they return all records beginning with 'test' THEN containing 'test' THEN ending with 'test'. ]

There is no guaranteed way that SQL Server will approach data collection. Order is only guaranteed when an ORDER BY clause is present.

UNION *cannot* return duplicates - but your definition of "duplicate" may be different from what SQL Server's definition actually is. A duplicate row is one that completely equals another row, column by column, every single column.

mattibutt

If I am correct your sp is inserting duplicates value in the table you need to put inside ifnot exists clause

auke_t

I think it's because of the [ID] int IDENTITY(1,1) field.

As every record gets it's own ID, there all unique.

Could you try to create the temptable without the identity, fill the table and then add the identity?

auke_t

Tested my hypothesis and it's not true.

So I agree with Cyberkiwi, your searchresults are not entirely duplicates

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial