REPEATED RECORDS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

REPEATED RECORDS

Author

Topic

paradise_wolf
Starting Member

32 Posts

Posted - 2007-02-21 : 06:17:23

Why this SQL procedure gives contiguous repeated records ( 3 or 4 times ) ?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ALTER PROCEDURE GetProductsOnPromotInDep
(@DepartmentID INT)
AS
SELECT Product.ProductID, Title
FROM Product INNER JOIN
(ProductCategory INNER JOIN
(Category INNER JOIN Department ON Department.DepartmentID = Category.DepartmentID)
ON ProductCategory.CategoryID = Category.CategoryID)
ON Product.ProductID = ProductCategory.ProductID
WHERE Category.DepartmentID = @DepartmentID
AND ProductCategory.CategoryID = Category.CategoryID
AND Product.ProductID = ProductCategory.ProductID
AND Product.OnPromotion = 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-21 : 06:26:39

Beacuse of the INNER JOINs.
Add a DISTINCT keyword to the query as

ALTER PROCEDURE GetProductsOnPromotInDep
(@DepartmentID INT)
AS
SELECT DISTINCT Product.ProductID, Title
FROM Product INNER JOIN 
(ProductCategory INNER JOIN
(Category INNER JOIN Department ON Department.DepartmentID = Category.DepartmentID)
ON ProductCategory.CategoryID = Category.CategoryID)
ON Product.ProductID = ProductCategory.ProductID
WHERE Category.DepartmentID = @DepartmentID
AND ProductCategory.CategoryID = Category.CategoryID
AND Product.ProductID = ProductCategory.ProductID
AND Product.OnPromotion = 1

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-02-21 : 06:27:43

Because a Product can be in multiple Categories, or a Category can be in multiple Departments ?

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-21 : 06:27:59

the tables relation is one to many ?

Without the table structure, sample data and explanation of the tables, i can only guess.

Kristen
Test

22859 Posts

Posted - 2007-02-21 : 06:29:12

Peso: I think paradise_wolf would be better off fixing the cause, rather than using a DISTINCT - as that requires more CPU etc. and IMHO just masks the underlying problem!

e.g. use an EXISTS instead of INNER JOIN

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-21 : 06:32:32

Many of the WHEREs can be omitted since they are already included in the JOIN.

ALTER PROCEDURE GetProductsOnPromotInDep
(
	@DepartmentID INT
)
AS

SET NOCOUNT ON

SELECT DISTINCT	Product.ProductID,
		Title
FROM		Product
INNER JOIN	ProductCategory ON ProductCategory.ProductID = Product.ProductID
INNER JOIN	Category ON Category.CategoryID = ProductCategory.CategoryID
INNER JOIN	Department ON Department.DepartmentID = Category.DepartmentID
WHERE		Category.DepartmentID = @DepartmentID
		AND Product.OnPromotion = 1

Yes. An EXISTS might be a better choice here.
How many records are we talking about in the different tables?

Peter Larsson
Helsingborg, Sweden

paradise_wolf
Starting Member

32 Posts

Posted - 2007-02-21 : 06:58:49

Thank you all, specially to Peter Larsson.

Your second solution is working !

{{Yes. An EXISTS might be a better choice here.}}

How should I use the EXISTS in this case ?

{{How many records are we talking about in the different tables?}}

The records are not repeated in the tables. The output of the my former procedure is what was giving repeated records.

Kristen
Test

22859 Posts

Posted - 2007-02-21 : 11:07:14

"The records are not repeated in the tables."

I don't think that was what Peter was referring to.

How many records in the tables: Product, ProductCategory, Category, Department ?

If its only a handful then using DISTINCT will be OK. If its lots & lots then worth putting the effort into changing the query to use EXISTS.

Personally I would do it anyway because who knows how big the tables might get!

Assuming that [Title] is in the [Product] table then it would be something like:


SELECT	Product.ProductID,
	Title
FROM	Product
WHERE	Product.OnPromotion = 1
	AND EXISTS
	(
		SELECT	*	
		FROM	ProductCategory
			JOIN Category
				ON Category.CategoryID = ProductCategory.CategoryID
			JOIN Department
				ON Department.DepartmentID = Category.DepartmentID
		WHERE	    ProductCategory.ProductID = Product.ProductID
			AND Category.DepartmentID = @DepartmentID
	)

Kristen

Kristen
Test

22859 Posts

Posted - 2007-02-21 : 11:29:38

same question being asked in a separate thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79462

Kristen

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-02-21 : 13:36:21

It also looks like you could lose the join to Department as it doesn't seem to be needed as you are filtering of DepartmentID from the Category.

-Ryan

Kristen
Test

22859 Posts

Posted - 2007-02-21 : 13:51:39

"It also looks like you could lose the join to Department"

I read the Query as "Only include it eh Product has one (or more) Categories that have one (or more) Departments" ...

Kristen

paradise_wolf
Starting Member

32 Posts

Posted - 2007-02-21 : 17:59:20

Hi Kristen

I am not sure if I understood why your procedure code is selecting all columns in “ SELECT * ”.

I would be grateful if you could explain that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources