Only apples SELECT distinct customerid FROM Customers c1 WHERE Type = 'Apple' AND not exists (select 1 from customers c2 where c2.customerid=c1.customerid and not Type = 'Apple') ;
Apples and Bananas SELECT distinct customerid FROM Customers c1 WHERE Type = 'Apple' AND exists (select 1 from customers c2 where c2.customerid=c1.customerid and Type = 'Banana') ; On 2/3/2011 9:40 AM, Igor Tandetnik wrote: > On 2/3/2011 12:26 PM, Puneet Kishor wrote: >> On Thursday, February 3, 2011 at 11:10 AM, Scott Baker wrote: >>> INSERT INTO Customers VALUES (NULL, 1239, 'Banana'); >> Your EntryID is INTEGER PRIMARY KEY, yet you are inserting NULLs. > That's how you tell SQLite to generate IDs automatically. > >> Your CustomerID seems like it should be unique, yet you have identical rows >> inserted. > It's not declared unique, why do you think it should be? > >> For example, what is the difference between the first and the second row? > EntryID. > >>> #1) Query for customers who *ONLY* bought apples >> SELECT * >> FROM Customers >> WHERE Type = 'Apple'; > That would also report customers that bought something else besides apples. > >>> #2) Query for customers who bought apples *AND* bananas >> SELECT * >> FROM Customers >> WHERE Type = 'Apple' OR Type = 'Banana'; > That would report customers that only bought apples, as well as those > that only bought bananas. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
