Thomas DILIGENT wrote:
> I have 2 tables:
>
> A: _ID autoinc primary key
> name text
> b integer foreign key to table B
>
> B: _ID autoinc primary key
> name text
>
> In sql:
> CREATE TABLE "A" ("_ID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "name"
> TEXT, "b" INTEGER NOT NULL DEFAULT '0')
> CREATE TABLE "B" ("_ID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "name"
> TEXT)
>
> I would like to select A records with regards to some criteria, and then B
> records matching the A records.
> I do not want to make a left join query because,
> First, in a concrete example, I may have several relations and this may lead
> to a very complex query.
> And secondly, the code that reads the result is generated and extracting
> records from a single result may be very difficult to implement (I don't
> event want to know if it's feasible).
>
> My first idea was to perform:
> 1) SELECT * FROM A WHERE name LIKE 'foo*'
> 2) SELECT * FROM B WHERE _ID IN (SELECT _ID FROM A WHERE name LIKE 'foo*')
Do you really mean this?
SELECT * FROM B WHERE _ID IN
(SELECT b FROM A WHERE name LIKE 'foo%');
This uses your foreign key column b to refer to the records in the B
table. Also, the like operator uses % as a wildcard, not *.
>
> So my question is:
> What is the most efficient to perform such queries ?
Using a join will be more efficient. This will give the same results.
select B.* from A join B on B._ID = A.b where A.name = 'foo%';
This query will be quite efficient if you have an index on the name
column of table A.
> Is there any kind of cache that could avoid re-performing the query on A
> records ?
There is no need for a cache if you use a join.
If you still want to use multiple statements you can create your own
cache using a temporary table.
create temp table cache as SELECT * FROM A WHERE name LIKE 'foo*';
SELECT * FROM B WHERE _ID IN (SELECT b FROM cache);
This temp table can be resued as often as needed. When you are done with
it simply drop the table (or close the database, since all temp tables
are dropped when a database is closed).
> Is that the purpose of views ?
In some ways. You can use a view to hide the complexity of a query.
create view foo_B as
select B.* from A join B on B._ID = A.b where A.name = 'foo%';
With this view defined you can now get the same results using a simpler
query.
select * from foo_B;
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
