Solved

# Find cid of customers who have ordered exactly the same set of products which are ordered by a customer with cid 'c0001'.

Posted on 2008-10-12
351 Views
I am doing an assignment for school which involves writing queries. I am done except for I stumped on one question:

Find cid of customers who have ordered exactly the same set of products which are ordered by a customer with cid 'c0001'.

I can't seem to think of anyway to determine that the sets are the same. I will post the table definitions for the assignment and what I have so far. It is due on Tuesday so a fast response would be appreciated.
``````SELECT c.cid
FROM customers c, orders o, orderline ol
WHERE c.cid = o.cid AND o.oid = ol.oid AND ol.pid IN(SELECT DISTINCT ol.pid FROM customers c, orders o, orderline ol WHERE c.cid = 'c0001' AND c.cid = o.cid AND o.oid = ol.oid);

-- We will drop the tables in case they exist from previous runs
drop table orderline;
drop table orders;
drop table products;
drop table categories;
drop table customers;

CREATE TABLE customers (
cid           char(5),
cname         char(15),
phone         char(12),
PRIMARY KEY (cid)
);
CREATE TABLE categories (
catid         char(2),
catname       char(12),
PRIMARY KEY (catid)
);
CREATE TABLE products (
pid           char(5),
pname         char(15),
unit          char(5),
price         float,
stock_qty     integer,
catid         char(2),
PRIMARY KEY (pid),
FOREIGN KEY (catid) REFERENCES categories
);
CREATE TABLE orders (
oid           char(5),
cid           char(5),
odate         date,
charge_amount float,
PRIMARY KEY (oid),
FOREIGN KEY (cid) REFERENCES customers
);
CREATE TABLE orderline (
lno           integer,
oid           char(5),
pid           char(5),
qty           integer,
price         float,
PRIMARY KEY (oid, lno),
FOREIGN KEY (oid) REFERENCES orders ON DELETE CASCADE,
FOREIGN KEY (pid) REFERENCES products
);
``````
0
Question by:bejhan
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 2

LVL 27

Expert Comment

ID: 22700711
Guidelines for solving this problem.

1. Get the count at customer level.
2. Join this results with another subquery which gets the count of orders for customer 'c0001'.
3. filter results with the count from step 1 matching with step 2.
0

LVL 32

Expert Comment

ID: 22704138
Answering homework questions can be tricky because Oracle often offers multiple ways of finding a solution and the responders may not be aware of the class topic. In your case, you need to identify two sets of results - those whose customer is c0001 and those who are not c0001 - and then compare them for equality. The comparison might take the form of a join, the use of something like the set operator intersect, or it may involve the use of a group by function with a "having" clause. Good luck!
0

LVL 1

Author Comment

ID: 22705732
sujith80, but the same count wouldn't necessarily mean the same set of items.
0

LVL 1

Author Comment

ID: 22706771
I am totally stumped on this:

I have made a queries that finds customers who have the same number of products as c0001 but  I don't know how to check if the products are all the same.
``````SELECT b.cid
FROM (SELECT c.cid, COUNT(DISTINCT ol.pid) AS Count
FROM customers c, orders o, orderline ol
WHERE c.cid = 'c0001' AND o.oid = ol.oid
GROUP BY c.cid) a,
(SELECT c.cid, COUNT(DISTINCT ol.pid) AS Count
FROM customers c, orders o, orderline ol
WHERE c.cid = o.cid AND o.oid = ol.oid
GROUP BY c.cid) b
WHERE a.count = b.count;
``````
0

LVL 27

Accepted Solution

sujith80 earned 200 total points
ID: 22709618
``````SQL> select * from tbl1;

CID PRODUCT
---------- --------------------
10 prod1
10 prod0
20 prod2
20 prod0
40 prod1
40 prod0
50 prod5
50 prod0
60 prod1
60 prod0
70 prod0

11 rows selected.

SQL> select distinct cid
2  from (
3  select
4   t.cid, t.product, X.cn, count(*) over (partition by t.cid) ct
5  from
6  tbl1 t,
7  (select cid, product , count(*) over() cn from tbl1 where cid = 40) X
8  where
9  t.product = X.product
10  )
11  where cn = ct and cid <> 40 ;

CID
----------
10
60

SQL> spool off
``````
0

LVL 1

Author Comment

ID: 22715491
Okay I figured it out just in time :) I was looking at the question the wrong way, trying to include those who met requirements instead of excluding those who didn't.
``````SELECT cid
FROM customers
WHERE cid <> 'c0001'
MINUS
SELECT c.cid
FROM customers c, orders o, orderline ol
WHERE c.cid <> 'c0001' AND c.cid = o.cid AND o.oid = ol.oid AND ol.pid NOT IN (SELECT ol.pid FROM orders o, orderline ol WHERE o.cid = 'c0001' AND o.oid = ol.oid)
MINUS
SELECT a.cid FROM (SELECT c.cid, COUNT(DISTINCT ol.pid) AS Count
FROM customers c, orders o,  orderline ol WHERE c.cid <> 'c0001' AND c.cid = o.cid AND o.oid = ol.oid GROUP BY c.cid) a, (SELECT COUNT(DISTINCT ol.pid) AS Count FROM orders o,  orderline ol WHERE o.cid = 'c0001' AND o.oid = ol.oid GROUP BY o.cid) b
WHERE a.count <> b.count;
``````
0

LVL 1

Author Comment

ID: 22715550
I did not see that sujith80 had posted a solution, my apologies.
0

LVL 1

Author Comment

ID: 22715551
I did not see that sujith80 had posted a solution, my apologies.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
###### Suggested Courses
Course of the Month5 days, 9 hours left to enroll