If you simply want a list of all files that are present and are not also present in set 0 (I'm not sure how 'duplicated' means anything different...)
SELECT f.name, f.set, f.hash FROM files f LEFT OUTER JOIN files f2 ON f2.name = f.name and f2.set = 0 WHERE f.set != 0 and f2.name is null -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Sanderson Sent: Wednesday, January 23, 2013 12:18 PM To: General Discussion of SQLite Database Subject: [sqlite] SQL query I have a database with many million rows with in it each representing a file. There are many duplicate files in the database and all files are hashed. The files are sub categorised into a number of sets, numbered 0 to 10 for example. Files do not need to be in every set. I need to select all files that are in any set other than 0 that are not duplicated/present in set 0 So a sample database might contain columns name set hash with sample data file1 0 ABCD file1 1 ABCD file1 3 EF01 file2 0 BCE2 file2 2 BCE2 file3 5 EE34 file4 0 EE22 My query would return file1 3 EF01 file3 5 EE34 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
