SQL query question

Question

I am working on a small project using an oracle database and I have the following tables:

CUSTOMER ( Cid, CName, City, Discount ) PRODUCT ( Pid, PName, City, Quantity, Price ) ORDERS ( OrderNo, Month, Cid, Aid, Pid, OrderedQuantity, Cost ) 

How to get the names of all customers who ordered all products?

For example, if customer x ordered product1, product2, and product3 (which are all products offered by the company), he will be selected. And if customer y ordered only product 1 and 2, but not 3, he will not be selected.

How can i achieve this?

Answer 1

You want "relational division".

 select * from customer c where not exists( -- There are no product select 'x' from product p where not exists( -- the customer did not buy select 'x' from orders o where o.cid = c.cid and o.pid = p.id)); 

or

 select c.cid ,c.name from customer c join orders o using(cid) group by c.id ,c.name having count(distinct o.pid) = (select count(*) from product); 

Here's a great article by Joe Selco that shows several ways to implement relational division (and variation): Separated We stand: SQL Relational Division

Answer 2

You can use group by and use the having to require the customer to order all products:

 select c.CName from Customers c join Orders o on o.Cid = c.Cid group by c.Cid , c.CName having count(distinct o.Pid) = (select count(*) from products) 

IMHO more readable than the approach of "relational division", but less effective.