I have a test set with the following real data 1.jpg 0 5DA4-CD3A-62DE-2F9D-4BD7-6E24-EACE-936D 1.jpg 1 5DA4-CD3A-62DE-2F9D-4BD7-6E24-EACE-936D 1.jpg 2 5DA4-CD3A-62DE-2F9D-4BD7-6E24-EACE-936D 1.jpg 3 5DA4-CD3A-62DE-2F9D-4BD7-6E24-EACE-936D 1.jpg 4 890B-4533-447E-6461-070E-FDB7-799E-1FB8
SELECT * FROM files WHERE hash NOT IN (SELECT hash FROM files WHERE set=0) returns an empty data set, but should return the item from set 4 The following does work select * from files where set > 0 and not exists (select * from files a where hash=files.hash and set=0); which is great and solves my problem, but I cant see why the first query doesn't work. On 30 January 2013 21:37, Paul Sanderson <sandersonforens...@gmail.com>wrote: > > Thanks All - duplicated means the content is the same as well as the name, > different is the filename is the same but the content is different. > > I need to refine my query to produce only one copy of any that is not in > set 0 > > file1 0 ABCD > file1 1 ABCD > file1 3 EF01 > file2 0 BCE2 > file2 2 BCE2 > file3 5 EE34 > file4 0 EE22 > file4 1 FF34 > file4 3 FF34 > file4 4 FF34 > > > My query would return > > file1 3 EF01 > file3 5 EE34 > file4 1 FF34, or file4 3 FF34, or file4 4 FF34 > > Thanks > > > -- Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
