T-SQL Query | [ The All Products Puzzle ] – In this puzzle we have to two table i.e. SpecialProductMaster table – this table contains master information of the products and Sales Table- This table is used for capture product sales. Here we to find out customers who have purchase all the products mentioned in the  SpecialProductMaster  Table that appears in Sales table for a single customer. In the below example only customer 345 purchased Maggi and surf so we need 345 only as the output. Please check out the sample input and expected output for details.

Sample Input

SpecialProductMaster

Sales

Expected Output

Rules/Restrictions

• The solution should be should use “SELECT” statement or “CTE”.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.

CREATE TABLE SpecialProductMaster
(
ID INT
,ProductName VARCHAR(10)
)
GO

INSERT INTO SpecialProductMaster(Id,ProductName)
VALUES
(1,'Maggi'),
(2,'Surf')

CREATE TABLE Sales
(
SaleID INT
,ProductName VARCHAR(10)
,CustID INT
,SaleDATE DATETIME DEFAULT GETDATE()
)
GO

INSERT INTO Sales(SaleID,ProductName,CustID)
VALUES
(112,'Surf',346)
(109,'Parle',345),
(101,'Maggi',345),
(201,'Surf',345),
(302,'Rin',122),
(303,'TV',142)

UPDATE – 20-Apr-2015 – Solution 1

--


--Solution 1

SELECT s.* FROM 
(
      SELECT Ar.CustID , Ar.ProductCount ,ProductName FROM
      (
            SELECT Ar.CustId , COUNT(*) OVER ( PARTITION BY Ar.CustID ORDER BY (SELECT NULL)) ProductCount , ProductName
            FROM ( SELECT m.ID,m.ProductName,s.CustID FROM Sales s INNER JOIN SpecialProductMaster m ON s.ProductName = m.ProductName ) Ar
      )
      Ar WHERE ProductCount =  ( SELECT COUNT(*) FROM SpecialProductMaster m )
) Br
INNER JOIN Sales s ON s.CustID = Br.CustID AND s.ProductName = Br.ProductName

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com