On 9/11/08, Thomas DILIGENT <[EMAIL PROTECTED]> wrote:
> Hi,
>
> 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)
>
You can simplify the above as
CREATE TABLE A (
_ID INTEGER PRIMARY KEY,
name TEXT,
b INTEGER DEFAULT 0
)
CREATE TABLE B (
_ID INTEGER PRIMARY KEY,
name TEXT
)
why? Because INTEGER PRIMARY KEY already provides AUTOINCREMENT, hence
NOT NULL (unless you want to set up a CONSTRAINT for NOT NULL as well
so you can throw and error).
> 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*')
>
> So my question is:
> What is the most efficient to perform such queries ?
> Is there any kind of cache that could avoid re-performing the query on A
> records ?
> Is that the purpose of views ?
>
Even though, from its name, it seems like TABLE A (b) is an implied
foreign key, your query above makes it seem other wise. If you want to
select all the records in TABLE B whose _ID match the _ID of the
records in table A (which, actually makes no sense because there is no
relationship between the two _ID columns as they are both INTEGER
PRIMARY KEY), then yes, the above queries will do what you want.
Are they efficient? Who knows? Doing a LEFT JOIN would probably be
more efficient, and a decent use of the database, but you don't want
to do that.
There is no cache in SQLite per se that can remember the results of
two separate queries, but you can easily build one in your application
which is using SQLite. Store the results of the first query in an
array, then use the array to perform the second query.
> Many thanks,
> Thomas
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
