How can I get particular product if in all category?

Question

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?

Answer 1

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

• SQLFiddle Demo

OUTPUT

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

Answer 2

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