> 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 set > 0 and not exists (select * from files a where hash=files.hash and set=0); --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
