question about SQL query

Question

I'm working on a small project involving 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 can retrieve the names of all customers who ordered all the products?

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

How can I achieve this?

Can you use plsql or only SQL? – m.genova Apr 03 '11 at 19:42 — m.genova, Apr 03 '11 at 19:42

Ronnis · Answer 1 · 2011-04-03T19:56:17.410

5

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 is a great article by Joe Celko that shows several ways of implementing relational division (and variations): Divided We Stand: The SQL of Relational Division

edited Apr 03 '11 at 19:56

answered Apr 03 '11 at 19:41

Ronnis

12,593
2
32
52

1

+1. If someone is interested on learning more about relational division, Joe Celko has written a pretty nice article about it here: http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/ – Kaitsu Apr 03 '11 at 19:52
@Kaitsu, I couldn't find my SQL for smarties, so I googled celko and relation division. Just finished reading the article when I saw your comment ;) – Ronnis Apr 03 '11 at 19:57

score 2 · Answer 2 · answered Apr 03 '11 at 19:45

You can use group by and use a having clause to demand that the customer has ordered all products there are:

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 "relational divison" approach, but less efficient.

question about SQL query

2 Answers2

Linked