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
