On Thursday, February 3, 2011 at 11:10 AM, Scott Baker wrote: > If I have the following (highly simplified) customer table how do I: > > #1) Query for customers who *ONLY* bought apples > #2) Query for customers who bought apples *AND* bananas > #3) Query for customers who bought exactly 2 apples? > > ---------------------------------------------------------------------- > > DROP TABLE IF EXISTS Customers; > CREATE Table Customers ( > EntryID INTEGER PRIMARY KEY, > CustomerID INT, > Type ENUM > ); > > INSERT INTO Customers VALUES (NULL, 1234, 'Banana'); > INSERT INTO Customers VALUES (NULL, 1234, 'Banana'); > INSERT INTO Customers VALUES (NULL, 1235, 'Apple'); > INSERT INTO Customers VALUES (NULL, 1236, 'Banana'); > INSERT INTO Customers VALUES (NULL, 1237, 'Banana'); > INSERT INTO Customers VALUES (NULL, 1237, 'Banana'); > INSERT INTO Customers VALUES (NULL, 1237, 'Apple'); > INSERT INTO Customers VALUES (NULL, 1238, 'Apple'); > INSERT INTO Customers VALUES (NULL, 1238, 'Apple'); > INSERT INTO Customers VALUES (NULL, 1239, 'Apple'); > INSERT INTO Customers VALUES (NULL, 1239, 'Banana');
Your EntryID is INTEGER PRIMARY KEY, yet you are inserting NULLs. Your CustomerID seems like it should be unique, yet you have identical rows inserted. For example, what is the difference between the first and the second row? That said, > #1) Query for customers who *ONLY* bought apples SELECT * FROM Customers WHERE Type = 'Apple'; > > #2) Query for customers who bought apples *AND* bananas SELECT * FROM Customers WHERE Type = 'Apple' OR Type = 'Banana'; > > #3) Query for customers who bought exactly 2 apples? SELECT CustomerID FROM Customers WHERE Type = 'Apple' GROUP BY CustomerID HAVING Count(CustomerID) = 2; _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
