Re: [sqlite] Concatenation question
Clark Christensen wrote: > > Long setup for a simple question: Is null the expected result when > one column of a concatenation operation is null? > Yes, that is the result required by the SQL standard. The result of a concatenation operator is NULL if either argument is NULL. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concatenation question
Clark Christensen <[EMAIL PROTECTED]> wrote: > Long setup for a simple question: Is null the expected result when > one column of a concatenation operation is null? Yes. Pretty much any operation where at least one operand is NULL produces a NULL. See also http://sqlite.org/nulls.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Concatenation question
Hello, Using SQLite v3.3.13, this query: select oid || '|' || email_addr || '|' || residual_value as RD from gl_claims c where --RD is not null and status = 1 and not exists (select 1 from gl_claim_tickets where ticket_type = 'coupon' and claim_id = c.oid); I expect one row of data. But what I get is 3 rows, the one I expect, and two others where RD is null. I've tracked it down to null values in residual_value for the null result rows. Since it's test data, and the rows in question are crap anyway, no big deal. I can easily work around it by un-commenting the null test in the where clause. Long setup for a simple question: Is null the expected result when one column of a concatenation operation is null? Thanks! -Clark ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backticks in Column Names
On Sep 11, 2008, at 1:11 PM, Gavin Kistner wrote: > So I ask again: can the inclusion of backticks in the column name > returned as the result for certain select statements be considered a > bug? In the absence of an AS clause, SQLite makes no promises about column names. If you want a specific column name, use an AS clause on that column to specify the name. Otherwise, you get what you get and what you get might change from one point release to the next. (That said, people tend to scream furiously when the column naming rules changes, so we do try to avoid changing them without a very good reason.) D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backticks in Column Names
On Sep 11, 2008, at 10:49 AM, Igor Tandetnik wrote: > Gavin Kistner <[EMAIL PROTECTED]> wrote: >> The presence of backticks in the column header name is causing the >> ORB >> library I'm working with to think that the name of the column is >> "`bar`" instead of "bar". >> See: >> http://groups.google.com/group/sequel-talk/browse_frm/thread/915c6f807f6d61c1 >> >> May this properly be considered a bug in SQLite? > > http://www.mail-archive.com/sqlite-users@sqlite.org/msg36030.html Thank you for the swift followup. I did search before posting, but was searching for "backtick" instead of "grave accent". Upon reading that thread, however, I'm not exactly sure how it applied. The thread mentions backticks as topic #2, but proposes removing the use of double-quoted (topic #3). All followup replies seem to be in favor of removing double-quotes, with no further mention of backticks/grave accents. Perhaps your intention in posting that link was to note that there are other ways to quote column names. As backticks are included for MySQL compatibility, and the library I'm utilizing is using[1] backticks (for presumably the same reason) I'm not at liberty to change how the quotation happens. So I ask again: can the inclusion of backticks in the column name returned as the result for certain select statements be considered a bug? [1] http://sequel.rubyforge.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backticks in Column Names
Gavin Kistner <[EMAIL PROTECTED]> wrote: > The presence of backticks in the column header name is causing the ORB > library I'm working with to think that the name of the column is > "`bar`" instead of "bar". > See: > http://groups.google.com/group/sequel-talk/browse_frm/thread/915c6f807f6d61c1 > > May this properly be considered a bug in SQLite? http://www.mail-archive.com/sqlite-users@sqlite.org/msg36030.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Backticks in Column Names
> SQLite version 3.5.6
> Enter ".help" for instructions
> sqlite> create table 'foo' (bar 'text');
> sqlite> insert into 'foo' values ('a');
> sqlite> .headers on
> sqlite> select * from foo;
> bar
> a
> sqlite> select "bar" from foo;
> bar
> a
> sqlite> select `bar` from foo;
> bar
> a
> sqlite> select `bar`,count(`bar`) as total from foo group by(`bar`);
> `bar`|total
> a|1
The presence of backticks in the column header name is causing the ORB
library I'm working with to think that the name of the column is
"`bar`" instead of "bar".
See:
http://groups.google.com/group/sequel-talk/browse_frm/thread/915c6f807f6d61c1
May this properly be considered a bug in SQLite?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Considerations with in-memory SQLite3
Dennis Cote wrote: > That looks right to me. > Thanks for the quick check, Dennis. > There is no need to create an index on the temporary id_list, since you > are going to be doing a full table scan of that table anyway. > >select * >from id_list >join mt.table on mt.table.id = id_list.id >order by id_list.id > > The only benefit of an index would be if you want the results returned > in id order, then the index would be used to optimize the order by > clause. If that is the case you can get the same effect by declaring the > id column as "integer primary key" in the id_list table. This will > eliminate the index and its redundant duplicate storage of the id_list data. > This is a very good point that I would not have guessed. My feeling would have been that an index would be needed since I was going to do a join on the data next. But, I see what you mean - something precious learned. Thanks! > For fastest operation the mt.table.id should also be an "integer primary > key" column as this will eliminate a rowid lookup operation if it is an > indexed column. > Yes, this I knew - I always keep an single field integer primary key for almost all my tables! Thanks for the reminder. Best regards Mohit. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Efficient query of 2 related tables
On 9/11/08, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > P Kishor <[EMAIL PROTECTED]> wrote: > > why? Because INTEGER PRIMARY KEY already provides AUTOINCREMENT > > > There's a difference in using INTEGER PRIMARY KEY with and without > AUTOINCREMENT keyword: http://www.sqlite.org/autoinc.html > > Thanks. Nice to know the "subtle" difference now. Of course, INTEGER PRIMARY KEY does provide auto-increment (just not AUTOINCREMENT) :-) > Igor Tandetnik > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Efficient query of 2 related tables
P Kishor <[EMAIL PROTECTED]> wrote: > why? Because INTEGER PRIMARY KEY already provides AUTOINCREMENT There's a difference in using INTEGER PRIMARY KEY with and without AUTOINCREMENT keyword: http://www.sqlite.org/autoinc.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Efficient query of 2 related tables
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
Re: [sqlite] Considerations with in-memory SQLite3
Mohit Sindhwani wrote:
>
> Since the database is to be created and deleted in a thread itself, I
> think I may need to do something like:
> * Create in-memory database (":memory:")
> * Attach the main database (from file) as 'mt'
> * Create the temporary table for id_list
> * Insert the user entered IDs
> * Create an index on it
> * Join and get the records of interest (between idlist.id and mt.table.id)
> * Do the processing
>
> Then, when I close the database, the in-memory stuff is all gone. Does
> that sound right?
>
That looks right to me.
There is no need to create an index on the temporary id_list, since you
are going to be doing a full table scan of that table anyway.
select *
from id_list
join mt.table on mt.table.id = id_list.id
order by id_list.id
The only benefit of an index would be if you want the results returned
in id order, then the index would be used to optimize the order by
clause. If that is the case you can get the same effect by declaring the
id column as "integer primary key" in the id_list table. This will
eliminate the index and its redundant duplicate storage of the id_list data.
For fastest operation the mt.table.id should also be an "integer primary
key" column as this will eliminate a rowid lookup operation if it is an
indexed column.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?
Lothar Behrens wrote: > Am 10.09.2008 um 17:37 schrieb Dennis Cote: > >> Lothar Behrens wrote: >>> What is the function to rollback a transaction or commit ? >>> I want also to break into these functions. If there is no way I try >>> to implement the rollback and commit callbacks. >>> Also the closing of the database would be interesting, or analysing >>> the data in the jornal. >> The journal file is closed when a transaction ends. This is done by >> the function pager_end_transaction() at line 28880 of the >> amalgamation. Note this function is called for both a rollback or a >> commit. >> > > Hi, > > I now have seen that many of my simple select statements automatically > does a rollback on behalv of OP_Halt. > > Also I have seen that an insert, update or delete statement does > automatically a commit in some circumstances as: > > * One VDBE is running only > * the statement hits an ON FAIL and have to commit in that case > * other circumstances I do not understand yet > > If I do understand all this correctly I have one case I may stuck into: > > A select statement (not readonly) is still open (having sqlite3_step() > returning SQLITE_ROW) and then > I have created an insert statement that is committed but the outer > transaction as of the select statement > does a rollback if closed later. > > Thus, this results in readable (just inserted) data but loses these > data because the outer rollback occurs. > > Right ? > > If so, then I have to redesign something as of this may be the case in > my usage of the database API :-) > > My database form opens a statement to select some data and navigates > to one (the first, the next or any other) and > leaves the statement open in a transaction I think (form A, database > A) as of a call to sqlite3_step() returning SQLITE_ROW. > > Then I open another database form (form B, database A) and try to add > some data. There is no Commit (sqlite3BTreeCommitPhaseOne) or > rollback (sqlite3BTreeRollback), so I assume the running transaction > from form A is causing this. > > Then when I close my application the transaction (form A, database A) > is rolled back and this loses my data changes. > > Right ? That is correct. You are in autocommit mode, so each SQL statement executes in its own transaction. The outer select starts a transaction. The insert does not start a transaction, since a transaction is already open (sqlite only has a single transaction open at any time). The application can see all the changes to the database (i.e. it can see the uncommitted data). If you now close the database before you reset or finalize the select query (which will commit the transaction it started), then the open transaction will be rolled back and the changes will be lost. > > So my solution would be this: > > Don't leave sqlite3_step() operations in SQLITE_ROW state. Better try > to finish until SQLITE_DONE to close the transaction. > > I can do this because: > > * I mostly read only the primary keys of a table (there it is done > automatically) to prepare for lazy load (pattern). > * I read the full data row for a specific primary key as of any cursor > activity. (That way I have simulated full cursor support) > > I hope with that I get solved this problem. > > Please comment, If there is something still wrong in my understanding. > That should work fine. You don't have to let the select run to completion if you don't want to. If you reset or finalize the select after it returns the last desired row, it will also close the transaction. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] dropping constraints
Igor Tandetnik wrote: "q10" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] Is there a way to drop a constraint from a table, something like this: ALTER TABLE table_name DROP CONSTRAINT constraint_name? Unfortunately, this command is not supported by SQLite3. Are there other possibilities? Rename old table, create new table the way you want it, copy data over, drop old table. Igor Tandetnik Additional performance tip for above - do not recreate the indices on the new table until you're done inserting the data. That way you'll save time. And, of course, don't forget to wrap things in a transaction. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Efficient query of 2 related tables
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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] dropping constraints
"q10" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Is there a way to drop a constraint from a table, something like this: > > ALTER TABLE table_name DROP CONSTRAINT constraint_name? > > Unfortunately, this command is not supported by SQLite3. Are there > other possibilities? Rename old table, create new table the way you want it, copy data over, drop old table. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Empty column: Set to NULL or leave empty?
On Thu, Sep 11, 2008 at 12:19:44PM +0200, Gilles Ganault scratched on the wall: > So I guess a NULL is different from a column that was never set to > anything when creating the record? There is no such thing as "a column that was never set to anything." You might not explicitly set a value, but something is always recorded into the record. > Here's an example: > == > //Leave col2 empty instead of NULL > $sql = "INSERT INTO mytable (id,col1) VALUES (NULL,?)"; > $insert = $dbh->prepare($sql); > $insert->execute(array($argv[1])); > == In this case, "id" will have a NULL assignment (even if it has a non-NULL default value) and "col2" will be assigned its default value. All columns have a default value. You can associate an explicit default value to a column when you create the table. Otherwise, the default value is assumed to be NULL. A NULL assignment is SQL's way of representing an undefined/unknown value. This is a bit different than the way many popular scripting languages use an "undef" value or the way that C/C++ uses NULL. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] getting extension-functions.c to work in SQLITE on WindowsXP
I would like to use the extension-functions.c from Liam Healy
(http://sqlite.org/contrib/download//download/extension-functions.c?get=25)
with SQLite. I am working on Windows XP and I have no clue how to
compile the file into a library. And after that there is the issue that
loading extensions is by default prohibited as a security measure.
From the intro text of the extension-fuctions.c file:
--
Note: Loading extensions is by default prohibited as a security measure;
see "Security Considerations" in
http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions. If the sqlite3
program and library are built this way, you cannot use these functions
from the program, you must write your own program using the sqlite3
API, and call sqlite3_enable_load_extension as described above.
If the program is built so that loading extensions is permitted, the
following will work: sqlite> SELECT
load_extension('./libsqlitefunctions.so'); sqlite> select
cos(radians(45)); 0.707106781186548
---
Therefore, I would like to asks;
1) did anybody compile and would like to share the extension-function.c
into a library so it can be loaded as an extension
2) how can I get a sqlite program version that does allow loading
extensions.
Many thanks
Paulo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Empty column: Set to NULL or leave empty?
"Gilles Ganault" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I didn't pay attention to this until recently, when someone advised > me to never set empty columns to NULL. > > So I guess a NULL is different from a column that was never set to > anything when creating the record? No. A column that was never explicitly set to anything, and doesn't have a declared default value, is set to NULL. Whoever told you otherwise is confused. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?
Am 10.09.2008 um 17:37 schrieb Dennis Cote: > Lothar Behrens wrote: >> What is the function to rollback a transaction or commit ? >> I want also to break into these functions. If there is no way I try >> to implement the rollback and commit callbacks. >> Also the closing of the database would be interesting, or analysing >> the data in the jornal. > > The journal file is closed when a transaction ends. This is done by > the function pager_end_transaction() at line 28880 of the > amalgamation. Note this function is called for both a rollback or a > commit. > Hi, I now have seen that many of my simple select statements automatically does a rollback on behalv of OP_Halt. Also I have seen that an insert, update or delete statement does automatically a commit in some circumstances as: * One VDBE is running only * the statement hits an ON FAIL and have to commit in that case * other circumstances I do not understand yet If I do understand all this correctly I have one case I may stuck into: A select statement (not readonly) is still open (having sqlite3_step() returning SQLITE_ROW) and then I have created an insert statement that is committed but the outer transaction as of the select statement does a rollback if closed later. Thus, this results in readable (just inserted) data but loses these data because the outer rollback occurs. Right ? If so, then I have to redesign something as of this may be the case in my usage of the database API :-) My database form opens a statement to select some data and navigates to one (the first, the next or any other) and leaves the statement open in a transaction I think (form A, database A) as of a call to sqlite3_step() returning SQLITE_ROW. Then I open another database form (form B, database A) and try to add some data. There is no Commit (sqlite3BTreeCommitPhaseOne) or rollback (sqlite3BTreeRollback), so I assume the running transaction from form A is causing this. Then when I close my application the transaction (form A, database A) is rolled back and this loses my data changes. Right ? So my solution would be this: Don't leave sqlite3_step() operations in SQLITE_ROW state. Better try to finish until SQLITE_DONE to close the transaction. I can do this because: * I mostly read only the primary keys of a table (there it is done automatically) to prepare for lazy load (pattern). * I read the full data row for a specific primary key as of any cursor activity. (That way I have simulated full cursor support) I hope with that I get solved this problem. Please comment, If there is something still wrong in my understanding. Thanks Lothar > HTH > Dennis Cote > -- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de Lothar Behrens Heinrich-Scheufelen-Platz 2 73252 Lenningen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Empty column: Set to NULL or leave empty?
Hello I didn't pay attention to this until recently, when someone advised me to never set empty columns to NULL. So I guess a NULL is different from a column that was never set to anything when creating the record? Here's an example: == //Leave col2 empty instead of NULL $sql = "INSERT INTO mytable (id,col1) VALUES (NULL,?)"; $insert = $dbh->prepare($sql); $insert->execute(array($argv[1])); == Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Efficient query of 2 related tables
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)
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 ?
Many thanks,
Thomas
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
