how do i use 'distinct' properly for my product query

I have a pr开发者_JS百科oduct catalog with

a product table (tblProducts : ProductID),

a product category table (tblProductCategories : CatID),

a product sub-category table (tblProductSubCategories : SubCatID), and

a xref table (tblProdXCat : ProductID,CatID,SubCatID)

that links the products to cats and subcats.

Products can be linked to multiple cats and/or subcats.

How can I query and get distinct products only (no product duplicated in result set)?

Microsoft SQL Server please.

Thanks!

I assume your tblProducts table is a table of distinct products, so you aren't asking how to select distinct products from thence.

If you mean how to get distinct products from tblProdXCat, then it's probably as simple as SELECT DISTINCT ProductID FROM tblProdXCat.

But maybe you want a complete information about the products rather than simply their IDs. In that case you could just INNER JOIN that list of distinct ProductID values against tblProducts:

SELECT p.*
FROM Products p
  INNER JOIN (SELECT DISTINCT ProductID FROM tblProdXCat) x
    ON p.ProductID = x.ProductID

If that is still not what you want then you may need to clarify your request.

select distinct productID from tblProducts

Put all the joining ambiguity into the WHERE clause.

SELECT *
FROM Products
WHERE ProductID in
(
  SELECT ProductId
  FROM Products JOIN ...
)