CODEWITHSUNDEEP
sql-server-2008t-sql
Abbas
Abbas

Reputation: 5044

How to get product which exists in multiple categories in sql server

I have 3 tables, namely product, category and productTocategory

Now my requirement is i want to check the products which exists in multiple categories.

i am not getting proper formation of t-sql.

Can anyone please help me build the query, below is my table structure.

  1. Product table has all production information. (ProductId, Name, Description.. etc)
  2. Category table has category and product information (CategoryId, Name etc)
  3. ProductToCategory has product and category information (ProductToCategoryID, CategoryID, ProductID)

Upvotes: 3

Views: 2565

Answers (1)

Karl Kieninger
Karl Kieninger

Reputation: 9129

You have to aggregate your many-to-many join table. Then you can use that in any number of ways to get the associate product info as needed.

For example:

CREATE TABLE Product (ProductId INT, Name VARCHAR(10))
CREATE TABLE Category(CategoryId INT, Name VARCHAR(10))
CREATE TABLE ProductToCategory (ProductToCategoryID INT, CategoryID INT, ProductID INT)

GO

INSERT INTO Product VALUES (1,'prod_1'),(2,'prod_2')
INSERT INTO Category VALUES (1,'cat_1'),(2,'cat_2')
INSERT INTO ProductToCategory VALUES (0,1,1),(1,2,1),(1,1,2)

GO 
SELECT p.* 
      ,a.CatCount
  FROM Product p
       INNER JOIN (
         SELECT ProductID 
               ,COUNT(CategoryId) CatCount
           FROM ProductToCategory 
          GROUP BY ProductID 
         HAVING COUNT(*) > 1
        ) a ON p.ProductId = a.ProductId

GO

DROP TABLE Product 
DROP TABLE Category
DROP TABLE ProductToCategory

Upvotes: 4

Related Questions