> sqlite> select * from aa where a_id1 in (select distinct a_id1 from ab > where ab.a_id2 = 1 and ab.b_id = 1) and a_id2 in (select distinct a_id1 > from ab where ab.a_id2 = 1 and ab.b_id = 1) ;
With your schema this can be transformed the same way: select aa.* from aa, ab ab1, ab ab2 where aa.a_id1 = ab1.a_id1 and ab1.a_id2 = 1 and ab1.b_id = 1 and aa.a_id2 = ab2.a_id1 and ab2.a_id2 = 1 and ab2.b_id = 1; Pavel On Thu, May 19, 2011 at 1:33 PM, Matthew Jones <matthew.jo...@hp.com> wrote: > First of all I couldn't for the life of we work out why that new query > would work but I'm sure that's just a limit of my knowledge. I then > realised that the database definition I had used was really very > different from what I was trying to do so I've had another go and then > tried to use the query with the new schema. Unfortunately, I have failed > miserably so here's the schema I'm working with now. > > Note that table b isn't used by I've created it to show that table ab is > a list of references of a records to b records and b records contain an a_id > > sqlite> create table a (a_id int primary key); > sqlite> create table b (a_id int, b_id int, primary key(a_id, b_id)); > sqlite> insert into a values(1); > sqlite> insert into a values(2); > sqlite> create table aa (a_id1 int, a_id2 int, primary key(a_id1, a_id2)); > sqlite> insert into aa values (1, 1); > sqlite> insert into aa values (1, 2); > sqlite> insert into aa values (2, 1); > sqlite> insert into aa values (3, 1); > sqlite> insert into aa values (1, 3); > sqlite> select * from aa; > 1|1 > 1|2 > 2|1 > 3|1 > 1|3 > sqlite> create table ab (a_id1 int, a_id2 int, b_id int, primary > key(a_id1, a_id2, b_id)); > sqlite> insert into ab values(1, 1, 1); > sqlite> insert into ab values(2, 1, 1); > sqlite> select * from aa where a_id1 in (select distinct a_id1 from ab > where ab.a_id2 = 1 and ab.b_id = 1) and a_id2 in (select distinct a_id1 > from ab where ab.a_id2 = 1 and ab.b_id = 1) ; > 1|1 > 1|2 > 2|1 > sqlite> select aa.* from aa ar, ab ab1, ab ab2; > Error: no such table: aa > sqlite> select aa.* from aa as ar, ab ab1, ab ab2; > Error: no such table: aa > sqlite> > > At this point I got confused but realised I had asked about a very > different schema and obviously must be doing something wrong as this > failed before adding any sort of where clause. > > >> From: Pavel Ivanov<paiva...@gmail.com> >> Subject: Re: [sqlite] Query efficiency >> >>> > That is, is leaving it to the >>> > query optimiser to figure out that I only need the sub select once the >>> > best thing to do? >> AFAIK, SQLite's optimizer is not that smart to collapse two identical >> sub-queries and reuse once generated result. >> >>> > Is the select I'm doing where both a_id1& 2 are "in" the exact same >>> > select the most efficient way to do this? >> I'd say that the following query will work faster in this particular >> case (with this set of tables and indexes): >> >> select ar.* >> from a_relation ar, ab ab1, ab ab2 >> where ar.a_id1 = ab1.a_id >> and ab1.b_id = 1 >> and ar.a_id2 = ab2.a_id >> and ab2.b_id = 1; >> >> But this query could be not transformable to your real case. Also >> performance in real schema could be different. >> >> >> Pavel >> >> >> On Tue, May 17, 2011 at 5:29 AM, Matthew Jones<matthew.jo...@hp.com> wrote: >>> > O.k. So this is a very cut down example but it illustrates the question: >>> > >>> > sqlite> create table a (a_id int primary key); >>> > sqlite> create table b (b_id int primary key); >>> > sqlite> create table ab (a_id int, b_id int, primary key(a_id, b_id)); >>> > sqlite> create table a_relation (a_id1 int, a_id2, primary key(a_id1, >>> > a_id2)); >>> > sqlite> select * from a_relation where >>> > ? ?...> a_id1 in (select a_id from ab where b_id = 1) and >>> > ? ?...> a_id2 in (select a_id from ab where b_id = 1); >>> > >>> > Is the select I'm doing where both a_id1& 2 are "in" the exact same >>> > select the most efficient way to do this? That is, is leaving it to the >>> > query optimiser to figure out that I only need the sub select once the >>> > best thing to do? >>> > >>> > (The actual tables in question are a little more complicated and I have >>> > versions to cope with but this effectively what I'm doing in C++ [so I'm >>> > preparing and binding etc.]. The actual sub select have a group by a_id >>> > to cope with multiple entries with different versions.) >>> > >>> > Thanks >>> > >>> > -- >>> > Matthew Jones >>> > Hewlett-Packard Ltd >>> > > > Thanks > > -- > Matthew Jones > Hewlett-Packard Ltd > _______________________________________________ > 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
