CODEWITHSUNDEEP
sqlsql-server
Alan S
Alan S

Reputation: 1

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

I have a product 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!

Upvotes: 0

Views: 127

Answers (3)

Andriy M
Andriy M

Reputation: 77707

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.

Upvotes: 1

Amy B
Amy B

Reputation: 110161

Put all the joining ambiguity into the WHERE clause.

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

Upvotes: 0

Beth
Beth

Reputation: 9617

select distinct productID from tblProducts

Upvotes: 0

Related Questions