How do I do this in SQL

[Yitzchok Dev]

I have these tables Customer Table: CustomerID int; PK FirstName nvarchar; LastNaem nvarchar; etc.; Products Table: ProductID int; PK Name varcahr; etc.; OrderItems Table: OrderItemID int; PK ProductID int; FK CustomerID int; FK etc.; Now I would like to get all users that have ordered: Product1 AND Product2, etc. Only those people that orders all the selected products -- modified at 21:15 Monday 10th September, 2007

[Chetan Patel]

Pass @Products --> with comma separated values like 'product1,product2' and no of items @ProductCount --> 2 Select * from Customers Where CustomerID in (Select CustomerID From (Select CustomerID from OrderItems Inner Join Products on Products .ProductID = OrderItems.productid Where [Name] IN (@products) Group by CustomerID,[Name]) as Temp Group by CustomerID having Count(CustomerID) = @productCount)

Best Regards, Chetan Patel

[Arjan Einbu]

This can't possibly work, can it? I mean, the WHERE [Name] IN (@Products) part specifically.

[Arjan Einbu]

You could create the inner query like this:

SELECT oi.CustomerID
FROM OrderItems oi
JOIN Products p
WHERE p.ProductID IN ('Product1', 'Product2')
GROUP BY oi.CustomerID
HAVING COUNT(*) = 2

You'll have to dynamically create the WHERE p.ProductID IN (...) part, and the value for the HAVING COUNT(*) = 2 should use a parameter. Joined with the customer table, the results could look something like this:

DECLARE @NumberOfProducts int
SET @NumberOfProducts = 2

SELECT
c.*
FROM Customer c
JOIN
(
SELECT oi.CustomerID
FROM OrderItems oi
JOIN Products p
WHERE p.ProductID IN ('Product1', 'Product2')
GROUP BY oi.CustomerID
HAVING COUNT(*) = @NumberOfProducts
) AS t
ON t.CustomerID = c.CustomerID

[John ph]

If this has to work then the query should get execute using "EXEC sp_executesql" command

Regards
J O H N :rose:
"Even eagles need a push." David McNally

---

[Yitzchok Dev]

Thank you