You need to start showing your testing....a .dump of your tables might help
plus show what you execute in the shell.
This works just fine and appears to produce what you want.
I don't know what you want #1 in your 1st question as the hash matches #0
which is what you said you want to exclude.
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('file1',0,'8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279');
sqlite> insert into files
values('file1',1,'8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279');
sqlite> insert into files
values('file1',2,'8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279');
sqlite> insert into files
values('file1',3,'B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5');
sqlite> insert into files
values('file1',4,'0546-4667-5A69-6478-FC97-6F27-840D-7D62');
sqlite> insert into files
values('file1',5,'0546-4667-5A69-6478-FC97-6F27-840D-7D62');
sqlite> insert into files
values('file1',6,'0546-4667-5A69-6478-FC97-6F27-840D-7D62');
sqlite> insert into files
values('file1',7,'01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29');
sqlite> insert into files
values('file1',8,'01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29');
sqlite> SELECT * FROM files WHERE hash NOT IN (SELECT hash FROM files WHERE
setid=0);
file1|3|B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5
file1|4|0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1|5|0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1|6|0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1|7|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
file1|8|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
sqlite> SELECT * FROM files WHERE hash NOT IN (SELECT hash FROM files WHERE
setid=0) group by hash;
file1|8|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
file1|6|0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1|3|B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5
sqlite> delete from files where setid=0;
sqlite> SELECT * FROM files WHERE hash NOT IN (SELECT hash FROM files WHERE
setid=0);
file1|1|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
file1|2|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
file1|3|B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5
file1|4|0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1|5|0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1|6|0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1|7|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
file1|8|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
sqlite> SELECT * FROM files WHERE hash NOT IN (SELECT hash FROM files WHERE
setid=0) group by hash;
file1|8|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
file1|6|0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1|2|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
file1|3|B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5
-----Original Message-----
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Sanderson
Sent: Wednesday, January 30, 2013 4:33 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQL query
Thanks - replace set with setid - query is the same (it was badly
simplified sorry)
Another real world example using the sql query
SELECT * FROM files WHERE hash NOT IN (SELECT hash FROM files WHERE
setid=0);
file1 0 8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
file1 1 8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
file1 2 8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
file1 3 B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5
file1 4 0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1 5 0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1 6 0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1 7 01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
file1 8 01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
I get an empty data set again, what I want is something like this
file1 1 8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
file1 3 B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5
file1 6 0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1 8 01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
also there could be instances where there is no setid=0 row, as below, in
this case I would want the same dataset as returned above
file1 1 8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
file1 2 8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
file1 3 B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5
file1 4 0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1 5 0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1 6 0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1 7 01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
file1 8 01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
On 30 January 2013 22:14, Michael Black <mdblac...@yahoo.com> wrote:
> '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: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] 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
> <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
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
