Solved

t-sql union not removing duplicate records

Posted on 2010-08-24
5
692 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:smacca
5 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33508522
[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.
0
 
LVL 11

Expert Comment

by:mattibutt
ID: 33508818
If I am correct your sp is inserting duplicates value in the table you need to put inside ifnot exists clause
0
 
LVL 9

Expert Comment

by:auke_t
ID: 33509027
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?
0
 
LVL 9

Expert Comment

by:auke_t
ID: 33509113
Tested my hypothesis and it's not true.

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

Accepted Solution

by:
lcohan earned 500 total points
ID: 33863400
Or you can put the big select with UNION ALL (for performance reasons) in a SQL View then SELECT DISTINCT column1, column2,.... all the ones you want to be DISTINCT (except the ID if its identity) from that view.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question