|
|
Posted by tizmagik on 04/17/07 22:51
Okay, this is what I have come up with so far:
SELECT c1.Customer_Name
FROM Customer c1
WHERE c1.CustomerID IN (
SELECT B.CustomerID
FROM Bought B
WHERE B.ItemID IN (
SELECT P.ItemID
FROM Prefer P, Customer c2
WHERE c2.Customer_Name = 'John'
AND c2.CustomerID = P.CustomerID ) )
But that brings me back to the problem where it will list customers
that bought *at least one* of the items that John prefers, not ALL of
the items that John prefers, that query gives:
John
Jeremy
Michelle
The expected answer is just 'Michelle' as being the only customer that
bought ALL of the items that John prefers with the following data:
- CUSTOMER table
CREATE TABLE Customer (
CustomerID int(4) NOT NULL,
Customer_Name varchar(30) NOT NULL,
Address varchar(30) NOT NULL,
PRIMARY KEY (CustomerID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO Customer VALUES (1000, 'John', '123 John St.');
INSERT INTO Customer VALUES (1001, 'Jeremy', '456 Jeremy Ave.');
INSERT INTO Customer VALUES (1002, 'Michelle', '789 Michelle Blvd.');
INSERT INTO Customer VALUES (1003, 'Laura', '1011 Laura Way');
INSERT INTO Customer VALUES (1004, 'Nicholas', '1004 Nicholas Place');
INSERT INTO Customer VALUES (1005, 'James', '1005 James Drive');
- ITEM table
CREATE TABLE Item (
ItemID int(11) NOT NULL,
Item_Name varchar(30) NOT NULL,
Manufacturer varchar(30) NOT NULL,
`Year` int(4) NOT NULL,
PRIMARY KEY (ItemID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO Item VALUES (9000, 'Camera', 'Nikkon', 1997);
INSERT INTO Item VALUES (9001, 'Camera', 'Sony', 1998);
INSERT INTO Item VALUES (9002, 'Camera', 'Olympus', 2001);
INSERT INTO Item VALUES (9003, 'Camera', 'Olympus', 2001);
INSERT INTO Item VALUES (9004, 'Camera', 'Polaroid', 1991);
INSERT INTO Item VALUES (9005, 'Laptop', 'Dell', 2006);
INSERT INTO Item VALUES (9006, 'Laptop', 'HP', 2005);
INSERT INTO Item VALUES (9007, 'Desktop', 'Dell', 2002);
INSERT INTO Item VALUES (9008, 'Desktop', 'Apple', 2004);
INSERT INTO Item VALUES (9009, 'PDA', 'Palm', 2003);
INSERT INTO Item VALUES (9010, 'PDA', 'Handspring', 1998);
INSERT INTO Item VALUES (9011, 'HDTV', 'Sony', 2004);
INSERT INTO Item VALUES (9012, 'HDTV', 'Samsung', 2005);
INSERT INTO Item VALUES (9013, 'HDTV', 'Toshiba', 2003);
INSERT INTO Item VALUES (9014, 'HDTV', 'Mitsubishi', 2003);
- BOUGHT table
CREATE TABLE Bought (
CustomerID int(4) NOT NULL,
ItemID int(4) NOT NULL,
`Date` date NOT NULL,
Quantity int(5) NOT NULL,
PRIMARY KEY (CustomerID,ItemID,`Date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO Bought VALUES (1002, 9000, '2007-04-01', 5);
INSERT INTO Bought VALUES (1002, 9001, '2007-04-30', 2);
INSERT INTO Bought VALUES (1002, 9008, '2007-04-09', 1);
INSERT INTO Bought VALUES (1002, 9014, '2007-04-15', 1);
INSERT INTO Bought VALUES (1001, 9001, '2007-04-16', 1);
INSERT INTO Bought VALUES (1001, 9008, '2007-04-16', 1);
INSERT INTO Bought VALUES (1000, 9008, '2007-04-16', 5);
INSERT INTO Bought VALUES (1000, 9001, '2007-04-17', 2);
INSERT INTO Bought VALUES (1005, 9003, '2007-04-16', 2);
INSERT INTO Bought VALUES (1004, 9002, '2007-04-16', 1);
INSERT INTO Bought VALUES (1001, 9011, '2007-02-16', 3);
INSERT INTO Bought VALUES (1001, 9010, '2007-02-16', 3);
INSERT INTO Bought VALUES (1003, 9012, '2007-02-16', 1);
INSERT INTO Bought VALUES (1005, 9013, '2007-02-16', 2);
INSERT INTO Bought VALUES (1004, 9006, '2007-04-01', 1);
- PREFER table
CREATE TABLE Prefer (
ItemID int(4) NOT NULL,
CustomerID int(4) NOT NULL,
PRIMARY KEY (ItemID,CustomerID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO Prefer VALUES (9000, 1000);
INSERT INTO Prefer VALUES (9001, 1000);
INSERT INTO Prefer VALUES (9002, 1004);
INSERT INTO Prefer VALUES (9003, 1003);
INSERT INTO Prefer VALUES (9006, 1001);
INSERT INTO Prefer VALUES (9007, 1004);
INSERT INTO Prefer VALUES (9007, 1005);
INSERT INTO Prefer VALUES (9008, 1000);
INSERT INTO Prefer VALUES (9008, 1002);
INSERT INTO Prefer VALUES (9008, 1004);
INSERT INTO Prefer VALUES (9009, 1002);
INSERT INTO Prefer VALUES (9013, 1005);
INSERT INTO Prefer VALUES (9014, 1000);
Again, any help is appreciated. (Yes, the professor didn't go over
AutoNumber fields yet in case you're wondering :) )
[Back to original message]
|