Filtering A Select Statement - 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 2000 Forums

SQL Server Development (2000)

Filtering A Select Statement

Author

Topic

dotnetallday
Starting Member

16 Posts

Posted - 2004-11-30 : 00:47:34

Since my knowledge of SQL is limited, I would like to pose a question to the incredibly knowledgeable folks here at sqlteam.com. I know where the error lies, but I don't know how to fix it. My query is below:
-----------------------
SELECT tblCatalog_SKUs.SKUID,
tblCatalog_SKUs.ProductID,
tblCatalog_Products.Name AS Name,
tblCatalog_Categories.Name AS Category
FROM tblCatalog_SKUs
INNER JOIN tblCatalog_Products ON tblCatalog_SKUs.ProductID = tblCatalog_Products.ProductID
INNER JOIN tblCatalog_ProductCategories ON tblCatalog_Products.ProductID = tblCatalog_ProductCategories.ProductID
INNER JOIN tblCatalog_Categories ON tblCatalog_ProductCategories.CategoryID = tblCatalog_Categories.CategoryID
WHERE FREETEXT(tblCatalog_SKUs.*, @SearchTerms)
AND tblCatalog_Products.Disabled = 0
AND tblCatalog_SKUs.Disabled = 0
AND tblCatalog_SKUs.SKUID = (
SELECT MIN(SKUID)
FROM tblCatalog_SKUs
WHERE tblCatalog_SKUs.ProductID = tblCatalog_Products.ProductID
)
-----------------------
tblCatalog_Products is related to tblCatalog_SKUs as 1 -> Many

tblCatalog_Categories is related to tblCatalog_ProductCategories as 1 -> Many

tblCatalog_Products is related to tblCatalog_ProductCategories as 1 -> Many

Basically, there can be many skus for each product, and each product and be in many categories.

This query returns a recordset where ProductID and SKUID are repeated if the product is in multiple categories. I'd like to return only a the distinct product ids. I've tried using the distinct() function, but that doesn't seem to work.

Any thoughts?

Thanks a million!

Jason A. Kiesel
Kiesel Media Group, Inc.
http://www.kmg.net
http://hosting.kmg.net

*ASP.NET Web Hosting Starting @ $9.99 / Month

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-30 : 08:06:10

how did you use distinct? Try it like below


SELECT Distinct
	tblCatalog_SKUs.SKUID,
	tblCatalog_SKUs.ProductID,
	tblCatalog_Products.Name AS Name,
	tblCatalog_Categories.Name AS Category
FROM tblCatalog_SKUs 
INNER JOIN tblCatalog_Products 
ON tblCatalog_SKUs.ProductID = tblCatalog_Products.ProductID 
INNER JOIN tblCatalog_ProductCategories 
ON tblCatalog_Products.ProductID = tblCatalog_ProductCategories.ProductID 
INNER JOIN tblCatalog_Categories 
ON tblCatalog_ProductCategories.CategoryID = tblCatalog_Categories.CategoryID 
WHERE FREETEXT(tblCatalog_SKUs.*, @SearchTerms)
AND tblCatalog_Products.Disabled = 0
AND tblCatalog_SKUs.Disabled = 0
AND tblCatalog_SKUs.SKUID = (SELECT MIN(SKUID) FROM tblCatalog_SKUs WHERE
	tblCatalog_SKUs.ProductID = tblCatalog_Products.ProductID)

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources