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

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

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.

Solution

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;