CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Tushar Maru
Tushar Maru

Reputation: 3431

How can I get particular product if in all category?

I have tables as below

CATEGORY TABLE

╔════╦═══════╗
║ ID ║ NAME  ║
╠════╬═══════╣
║  1 ║ cat 1 ║
║  2 ║ cat 2 ║
║  3 ║ cat 3 ║
╚════╩═══════╝

PRODUCT table

╔════╦════════════╦═════════════╗
║ ID ║ CATEGORYID ║ PRODUCTNAME ║
╠════╬════════════╬═════════════╣
║  1 ║          1 ║ product1    ║
║  2 ║          1 ║ product2    ║
║  3 ║          1 ║ product3    ║
║  4 ║          2 ║ product1    ║
║  5 ║          2 ║ product2    ║
║  6 ║          3 ║ product1    ║
║  7 ║          3 ║ product3    ║
╚════╩════════════╩═════════════╝

OUTPUT table

╔════╦════════════╦═════════════╗
║ ID ║ CATEGORYID ║ PRODUCTNAME ║
╠════╬════════════╬═════════════╣
║  1 ║          1 ║ product1    ║
║  4 ║          2 ║ product1    ║
║  6 ║          3 ║ product1    ║
╚════╩════════════╩═════════════╝

As in each category there is a product name "product1" so I need a list of products that are available in all the category.

Can any one help me how to do it with SQL Server T-SQL query?

Upvotes: 3

Views: 153

Answers (4)

msi77
msi77

Reputation: 1632

Via EXISTS

SELECT  a.*
FROM    Product a
where exists
        (
            SELECT  1
            FROM    Product
            where a.ProductName = Product.ProductName
            HAVING  COUNT(DISTINCT CategoryID) = (SELECT COUNT(*) FROM Category)
        )

Upvotes: 0

user359040
user359040

Reputation:

Try:

with cte as
(select p.*, count(*) over (partition by PRODUCTNAME) cats
 from product p)
select ID, CATEGORYID, PRODUCTNAME
from cte p
join (select count(*) totcats from category) c
on p.cats=c.totcats

Upvotes: 1

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Option with NOT EXISTS and EXCEPT operators

SELECT *
FROM dbo.Product p
WHERE NOT EXISTS (
                  SELECT p3.[ID]
                  FROM dbo.Category p3                  
                  EXCEPT
                  SELECT p2.[CAtegoryID]
                  FROM dbo.Product p2
                  WHERE p2.[productName] = p.[productName]                        
                  )

Demo on SQLFiddle

For improving performance use this index

CREATE INDEX ix_ID_Category ON Category(ID)

Upvotes: 0

John Woo
John Woo

Reputation: 263933

SELECT  a.*
FROM    Product a
        INNER JOIN
        (
            SELECT  ProductName
            FROM    Product
            GROUP   BY ProductName
            HAVING  COUNT(DISTINCT CategoryID) = (SELECT COUNT(*) FROM Category)
        ) b ON a.ProductName = b.ProductName

OUTPUT

╔════╦════════════╦═════════════╗
║ ID ║ CATEGORYID ║ PRODUCTNAME ║
╠════╬════════════╬═════════════╣
║  1 ║          1 ║ product1    ║
║  4 ║          2 ║ product1    ║
║  6 ║          3 ║ product1    ║
╚════╩════════════╩═════════════╝

Upvotes: 4

Related Questions