'set' is a reserved word. I get an error running your select statement.
Change it.
SQLite version 3.7.14.1 2012-10-04 19:37:12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table files(file,setid,hash);
sqlite> insert into files
values('1.jpg',0,'5DA4-CD3A-62DE-2F9D-4BD7-6E24-EACE-936D');
sqlite> insert into files
values('1.jpg',1,'5DA4-CD3A-62DE-2F9D-4BD7-6E24-EACE-936D');
sqlite> insert into files
values('1.jpg',2,'5DA4-CD3A-62DE-2F9D-4BD7-6E24-EACE-936D');
sqlite> insert into files
values('1.jpg',3,'5DA4-CD3A-62DE-2F9D-4BD7-6E24-EACE-936D');
sqlite> insert into files
values('1.jpg',4,'890B-4533-447E-6461-070E-FDB7-799E-1FB8');
sqlite> SELECT * FROM files WHERE hash NOT IN (SELECT hash FROM files WHERE
setid=0);
1.jpg|4|890B-4533-447E-6461-070E-FDB7-799E-1FB8
-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of Paul Sanderson
Sent: Wednesday, January 30, 2013 4:05 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQL query
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
<[email protected]>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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
