REPEATED RECORDS ( ROWS )
-
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
(
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-
cannot tell without knowing what your table structures look like and what sample data you are working with where you are seeing records repeated. please post that info and i am sure that somebody can help you out
-
Hi Chuck
I solved the problem by modifying the code as below.
Now I want to know how to use EXISTS instead INNER JOIN in the below procedure ?
Can somebody help me ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-
inner joining to a table allows you to actually select information out of the table you are joining to. using the exists statement simply returns a true or false on the condition you are using the exists statement against. The exists statements is used for subquerying.
BOL has information about the EXISTS statement that can help you out.
-
Thank you Chuck.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply