-3

I want to write a query that give names of those customers only who have bought all products whose price is less than 5. Whereas, my query gives all customers who have bought even a single product whose price is less than 5.

SELECT Customers.CompanyName AS Customers
FROM Customers
INNER JOIN Orders
ON Orders.CustomerID = Customers.CustomerID
JOIN [Order Details]
ON [Order Details].OrderID = Orders.OrderID
JOIN Products
ON Products.ProductID = [Order Details].ProductID
WHERE [Order Details].ProductID  IN (
  SELECT Products.ProductID FROM Products WHERE Products.UnitPrice < 5
)
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
uzair lodhi
  • 11
  • 5

1 Answers1

0

You can try the following:

SELECT Customers.CompanyName AS Customers
FROM Customers
INNER JOIN Orders
ON Orders.CustomerID = Customers.CustomerID
JOIN [Order Details]
ON [Order Details].OrderID = Orders.OrderID
JOIN Products
ON Products.ProductID = [Order Details].ProductID
WHERE [Order Details].ProductID  IN (
  SELECT Products.ProductID FROM Products WHERE Products.UnitPrice < 5)
GROUP BY CompanyName 
HAVING COUNT([Order Details].ProductID) = (SELECT Count(Products.ProductID) FROM Products WHERE Products.UnitPrice < 5)

I Included the GROUP BY and Having Clause so that you can get only the customers who have bought all the products with unit price less than 5

Stuart Black
  • 56
  • 6
  • This query is giving error: Each GROUP BY expression must contain at least one column that is not an outer reference. – uzair lodhi Apr 24 '19 at 08:42
  • Just tested this on the northwind database and it works, made an edit to fix a ) that was omitted – Stuart Black Apr 24 '19 at 09:03
  • I fixed that bracket ')' problem and after that i encountered the above error 'Each GROUP BY expression must contain at least one column that is not an outer reference'. – uzair lodhi Apr 24 '19 at 09:58