Solved

t-sql union not removing duplicate records

Posted on 2010-08-24
5
680 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
Comment Utility
[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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now