Thanks Michael - my test table has a lot of data in it - I need (tomorrow -
its 11pm here) to create subset of my test data and have a play.
thanks
On 30 January 2013 22:47, Michael Black <mdblac...@yahoo.com> wrote:
> 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
>
--
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
