Select all CustomerNames that have bought all the products that have been bought by the Customer with the id 'CENTC'

Question

I am using the Northwind database

For now i have tried

It is where i select the Orders of the Client

select od.ProductID from Customers c JOIN
Orders o on c.CustomerID=o.CustomerID
JOIN [Order Details] od on o.OrderID=od.OrderID
where c.CustomerID='CENTC'

And here is my solution

select distinct c.CompanyName, sum(od.ProductID) as suma from Customers c JOIN
Orders o on c.CustomerID=o.CustomerID
JOIN [Order Details] od on o.OrderID=od.OrderID
where od.ProductID = '40' or od.ProductID = '11'
group by c.CompanyName
having sum(od.ProductID)='51'

But it's a one use solution so i am not satisfied.

Answer 1

You can use an IN subquery for this

SELECT
  c.CompanyName,
  c.ContactName,
  SUM(od.quantity) AS quantity
FROM Customers c
JOIN Orders o on c.CustomerID = o.CustomerID
JOIN OrderDetails od on o.OrderID = od.OrderID
WHERE od.ProductID IN (
    SELECT od2.ProductID
    FROM Orders o2
    JOIN OrderDetails od2 on o2.OrderID = od2.OrderID
    WHERE o2.CustomerID = 'CENTC'
)
GROUP BY
  c.CustomerID,
  c.CompanyName,
  c.ContactName;