On Wed, Jan 23, 2013 at 12:17 PM, Paul Sanderson < sandersonforens...@gmail.com> wrote:
> 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 > SELECT * FROM files WHERE hash NOT IN (SELECT hash FROM files WHERE set=0) > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
