bejhan
asked on
Find cid of customers who have ordered exactly the same set of products which are ordered by a customer with cid 'c0001'.
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.
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),
address char(20),
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
);
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!
ASKER
sujith80, but the same count wouldn't necessarily mean the same set of items.
ASKER
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.
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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;
ASKER
I did not see that sujith80 had posted a solution, my apologies.
ASKER
I did not see that sujith80 had posted a solution, my apologies.
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.