Re: [sqlite] Order of UNION query results

[luuk34]

On 22-01-11 00:53, Josh Gibbs wrote:
> Could someone please clarify for me if the the resulting order of a UNION
> query will come back with the left data first, then the right data in 
> the case
> that no ordering has been defined for the query.
>
> My need is to have a parameter stored in a database, with an optional
> overriding parameter which should take precedence, such as:
>
> select value from param_overrides where key='setting' UNION
> select value from params where key='setting'
>
> I'd like the resulting recordset to always contain the override parameter
> first if it exists so I can simply use that value.
>
> Thanks, Josh
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

select value, 1 as myorder from param_overrides where key='setting' UNION
select value, 2 as myorder from params where key='setting'
order by 2;



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] max length of integer?

[luuk34]

On 10-01-11 13:09, Simon Slavin wrote:
> On 10 Jan 2011, at 10:05am, KimTaein wrote:
>
>> What is the largest number that I can store in integer data type in sqlite?
>> I was not able to find it on sqlite website.
> You're right.  It's not in the obvious place.  You can, however, find it here:
>
> 
>
> If I understand it correctly, integers can take up 64 bits, so the largest 
> number would be about
>
> 1.84467441e19
>
sqlite> select typeof((9223372036854775807)) ;
integer
sqlite> select typeof((9223372036854775808)) ;
real
sqlite>

so, i think, its 2^64-1


Luuk


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] calculate difference of two times and show the result as double value

[luuk34]

On 10-01-11 08:42, Sven wrote:
> Dear SQLite users,
>
> I've created a Microsoft Access 2003 database with three complex queries 
> written in pure SQL language. The function of the database is to 
> document and calculate the daily work-time.
>
> Today I want to convert this database to SQLite but I have problems with 
> my written queries: How can I calculate the difference of two times and 
> show the result as double value?
>
> For more information I'll include one example: One table hold the two 
> times (start_time, end_time) and the calculated result (shown in the 
> table as 'difference'):
>
> [code]
> start_time | end_time | difference
> 08:00:00 | 16:30:00 | 8.5
> [/code]
>
> The result of the difference between the two times (start_time, 
> end_time) shown in the table above as 'difference' will be calculated 
> with the following SQL-query code within Microsoft Access 2003:
>
> [code]
> ( [end_time] - [start_time] ) * 24
> [/code]
>
> and returns the following data '8.5'. While using the following query 
> code within SQLite:
>
> [code]
> select time(end_time) - time(start_time)
> [/code]
>
> I only get the following data returned: '8' (but not '8.5' as calculated 
> with SQL).
>
> So currently I don't know what to do next to solve my problem and 
> perhaps all the other users of this SQLite-users mailing list could help 
> me out.
>
> best regards
select (strftime('%s', time('now', '4 minutes')) - strftime('%s',
time('now')));
returns: 240(4*60)

This was found via Google at:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg56060.html

More help on the strftime function:
http://sqlite.org/lang_datefunc.html
http://pubs.opengroup.org/onlinepubs/007908799/xsh/strftime.html

-- 
Luuk

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Documentation v2

[luuk34]

On 10-12-10 19:02, Tito Ciuro wrote:
> Hello Dagdamor,
>
> Does it only work for Explorer? No Linux or Mac support?
>
> -- Tito
>

*http://tinyurl.com/2vr3dal
^^ chm readers/viewers for linux...
*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select

[luuk34]

On 10-12-10 12:43, Kees Nuyt wrote:
> On Fri, 10 Dec 2010 02:53:32 -0800 (PST), lucavb
>   wrote:
>
>> Hello,
>> i need to to do this:
>>
>> i have this table: "userrates", composed by: id, userid, idtitle, rate.
>>
>> for example:
>> (1, 12, 1, 3)
>> (2, 15, 99, 4)
>> (3, 22, 10, 1)
>> (10, 22, 1, 5)
>> (5, 166, 37, 1)
>> (4, 244, 10, 2)
>> (6, 298, 1, 4)
>> (7, 298, 10, 3)
>> (8, 298, 15, 2)
>>
>> i need to extract only the rows with the userid
>> who had voted both fims (1 and 10):
>>
>> the result will be:
>> (3, 22, 10, 1)
>> (10, 22, 1, 5)
>> (6, 298, 1, 4)
>> (7, 298, 10, 3)
>>
>> How can i do that?
>>
> Assuming you mean idtitle where you write "fims":
>
> SELECT a.id, a.userid, a.idtitle, a.rate
>FROM userrates AS a
>INNER JOIN (
>   SELECT userid
> FROM userrates
>  WHERE idtitle IN (1,10)
>  GROUP BY userid
>  HAVING count(id) = 2
>   ) AS b ON b.userid = a.userid
>   WHERE a.idtitle IN (1,10)
> ORDER BY a.userid,a.id;

A user who has rated movie 1 twice
and has not rated movie 10,
would show up in your result..

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select

[luuk34]

On 10-12-10 12:03, luuk34 wrote:
>
>
> On 10-12-10 11:53, lucavb wrote:
>> Hello,
>> i need to to do this:
>>
>> i have this table: "userrates", composed by: id, userid, idtitle, rate.
>>
>> for example:
>> (1, 12, 1, 3)
>> (2, 15, 99, 4)
>> (3, 22, 10, 1)
>> (10, 22, 1, 5)
>> (5, 166, 37, 1)
>> (4, 244, 10, 2)
>> (6, 298, 1, 4)
>> (7, 298, 10, 3)
>> (8, 298, 15, 2)
>>
>> i need to extract only the rows with the userid who had voted both 
>> fims (1
>> and 10):
>>
>> the result will be:
>> (3, 22, 10, 1)
>> (10, 22, 1, 5)
>> (6, 298, 1, 4)
>> (7, 298, 10, 3)
>>
>> How can i do that?
>>
>> Thanks in advance,
>>
>> Luca
>
> untested, something like:
>
> SELECT a.id, a.userid, a.idtitle, a.rate
> FROM userrates a
> INNER JOIN userrates b ON a.userid=b.userid AND b.idtitle=10
> WHERE a.idtitle=1
>
>

oops...

SELECT a.id, a.userid, a.idtitle, a.rate
FROM userrates a
INNER JOIN userrates b ON a.userid=b.userid AND b.idtitle=10
WHERE a.idtitle=1
UNION
SELECT a.id, a.userid, a.idtitle, a.rate
FROM userrates a
INNER JOIN userrates b ON a.userid=b.userid AND b.idtitle=1
WHERE a.idtitle=10



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select

[luuk34]

On 10-12-10 11:53, lucavb wrote:
> Hello,
> i need to to do this:
>
> i have this table: "userrates", composed by: id, userid, idtitle, rate.
>
> for example:
> (1, 12, 1, 3)
> (2, 15, 99, 4)
> (3, 22, 10, 1)
> (10, 22, 1, 5)
> (5, 166, 37, 1)
> (4, 244, 10, 2)
> (6, 298, 1, 4)
> (7, 298, 10, 3)
> (8, 298, 15, 2)
>
> i need to extract only the rows with the userid who had voted both fims (1
> and 10):
>
> the result will be:
> (3, 22, 10, 1)
> (10, 22, 1, 5)
> (6, 298, 1, 4)
> (7, 298, 10, 3)
>
> How can i do that?
>
> Thanks in advance,
>
> Luca

untested, something like:

SELECT a.id, a.userid, a.idtitle, a.rate
FROM userrates a
INNER JOIN userrates b ON a.userid=b.userid AND b.idtitle=10
WHERE a.idtitle=1


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reading a text file and insert to sqlite tables

[luuk34]

On 09-12-10 18:53, yazdan asgari wrote:
>
> --- On Thu, 12/9/10, luuk34<luu...@gmail.com>  wrote:
>
> From: luuk34<luu...@gmail.com>
> Subject: Re: [sqlite] Reading a text file and insert to sqlite tables
> To: "General Discussion of SQLite Database"<sqlite-users@sqlite.org>
> Date: Thursday, December 9, 2010, 9:21 AM
>
>
>
> On 09-12-10 18:13, yazdan asgari wrote:
>> Hi
>> I use C Programming Language and I also know that I could use INSERT 
>> command. But my problem is whether any one could show me a simple code which 
>> demonstrate how an INSERT command should be written during reading a text 
>> file. I have searched Google but I could not find any useful link.
>> Yazdan
>>
> u did not search correctly:
> http://www.google.com/search?hl=en=off=1G1ACAW_NLNL390=sqlite+c++example+insert+data=Search=f===_rfai=
>
> 1st link points to:
> http://www.lemoda.net/sqlite/c-insert/
>
> Hi
> Thanks for your quick reply. I saw the linked that you said but my problem is 
> to INSERT data during reading a long text file in which every line consists 
> of several queries. I could read this file and write to an output file but I 
> am not able to write a sqlite command (INSERT in this case) in order to put 
> data in desired tables.
>
>
i fixed te top-quoot, because i think bottom-quoot is easier to read.

But reading you question again, i dont seem to understand your 
problem... sorry

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reading a text file and insert to sqlite tables

[luuk34]

On 09-12-10 18:13, yazdan asgari wrote:
> Hi
> I use C Programming Language and I also know that I could use INSERT command. 
> But my problem is whether any one could show me a simple code which 
> demonstrate how an INSERT command should be written during reading a text 
> file. I have searched Google but I could not find any useful link. 
> Yazdan
>

u did not search correctly:
http://www.google.com/search?hl=en=off=1G1ACAW_NLNL390=sqlite+c++example+insert+data=Search=f===_rfai=

1st link points to:
http://www.lemoda.net/sqlite/c-insert/


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Bulk] How to optimize this simple select query ?

[luuk34]

On 09-12-10 17:27, Vander Clock Stephane wrote:
> no one have an idea how to do such query ??
>
> thanks
> stéphane
>
> On 12/8/2010 7:56 PM, Vander Clock Stephane wrote:
>> Hello,
>>
>> on the table :
>>
>> CREATE TABLE HASH(
>> .
>>  x5_y5>=<#randomnumber73>   and
>>  x5_y5<=<#randomnumber73>   + 20;
>>
>> because they takes very very lot of time (hourS) to return :(
>> on other SGBD (like Firebird) with same amount of data
>> they return immediatly ...
>
If you have another situation,
wit same amount of data,
which returns immedialty,
than either situation is not the same,
or you are making an error.

maybe creating indexes on more than one field,
  can speed up the query,
but i cannot tell you on which fields,
because i dont know the data

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Documentation

[luuk34]

On 04-12-10 17:30, Dagdamor wrote:
> luuk34<luu...@gmail.com>  писал(а) в своём письме Sat, 04 Dec 2010
> 20:24:45 +0600:
>> On 04-12-10 14:15, Max Vlasov wrote:
>>> Serge, I don't know what's so special about my chm viewer (bundled with
>>> Windows 7), but I can only see the list of articles in the contents and
>>> in
>>> the search results on the left, the right pane always shows an error
>>> about
>>> canceling moving to a web-page.
>>>
>>> Max
>> right click the file in explorer, and choose 'unblock' (on the
>> general-tab)
>> its there for 'safety'-purposes... ;-)
>>
>> you should, of course, only do this if you trust the docs...
>>
> Perhaps this is true, I'm still using win98, it's less restrictive :)
>
> The documents in the .chm are exact copies of the official documentation
> files - I haven't touched them, didn't even add missing  and
>   :P
> It should be safe to open.
>

i  should have added a link to microsoft, where they explain this:
http://support.microsoft.com/kb/902225


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Documentation

[luuk34]

On 04-12-10 14:15, Max Vlasov wrote:
> On Sat, Dec 4, 2010 at 11:51 AM, Dagdamor  wrote:
>
>> Hello.
>>
>> Would you like to have a downloadable version of the SQLite Documentation?
>> I've created one in Windows HTML Help (.chm) format. It seems to be useful,
>> single-file (no need to zip/unzip it), compact and easy to navigate/search.
>> I've uploaded a copy to my server:
>>
>
> Serge, I don't know what's so special about my chm viewer (bundled with
> Windows 7), but I can only see the list of articles in the contents and in
> the search results on the left, the right pane always shows an error about
> canceling moving to a web-page.
>
> Max

right click the file in explorer, and choose 'unblock' (on the general-tab)
its there for 'safety'-purposes... ;-)

you should, of course, only do this if you trust the docs...

> ___
> 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

Re: [sqlite] [ERROR] Failed compound query with simple expr in ORDER BY

[luuk34]

On 26-11-10 13:02, Drake Wilson wrote:
> Quoth luuk34<luu...@gmail.com>, on 2010-11-26 12:49:53 +0100:
>> The extra column seems to work,
>> but i thought this should work too?
> I would imagine so, at first glance.
>
>> But the ORDER is wrong...
> How?  The example you provided seems properly sorted.
>
>

oeps, i made an error in the order by.
so, indeed its working OK...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [ERROR] Failed compound query with simple expr in ORDER BY

[luuk34]

On 26-11-10 12:37, Drake Wilson wrote:
> Quoth Waldemar Derr, on 2010-11-26 12:24:27 +0100:
>> --Don't working: (Error: 1st ORDER BY term does not match any column in the
>> result set.)
>>
>> SELECT * FROM OrderTest WHERE Price<  200
>> UNION
>> SELECT * FROM OrderTest WHERE Price>  500
>> ORDER BY Price IS 0, Price;
>  From http://sqlite.org/lang_select.html:
> | Otherwise, if the ORDER BY expression is any other expression, it is
> | evaluated and the the returned value used to order the output rows. If
> | the SELECT statement is a simple SELECT, then an ORDER BY may contain
> | any arbitrary expressions. However, if the SELECT is a compound
> | SELECT, then ORDER BY expressions that are not aliases to output
> | columns must be exactly the same as an expression used as an output
> | column.
>
>> Is this a bug? In MySQL it works as expected. Is there a workaround?
> Add another output column with the expression you want, give it a
> name, and ORDER BY that name.
>
> My guess is that this is because the multiple output column
> specification parts of a compound SELECT may result in different
> intrepretations of an arbitrary expr in that position, and it's
> not clear how any kind of inwards propagation would work without
> yielding surprising results.
>
> --->  Drake Wilson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

The extra column seems to work,
but i thought this should work too?
But the ORDER is wrong...

sqlite> SELECT a,b FROM (
...> SELECT ID a, Price b FROM OrderTest WHERE Price < 200
...> UNION
...> SELECT ID a, Price b FROM OrderTest WHERE Price > 500
...> )
...> ORDER BY a IS 0, b;
a|b
3|0.0
4|25.0
1|50.0
2|75.0
5|100.0
7|1000.0
8|1.0
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Key Value pairs in a table

[luuk34]

On 17-11-10 10:32, Arigead wrote:
> Hi all,
>  I've started to use an existing Database with C source code. As I'm new 
> to
> all this I can't moan about database design but I'm sure that Key Value pairs
> in a Database table suits modern languages like Python down to the ground. It
> ain't suiting me though ;-)
>
> I'm really struggling to find a solution to my problems so I though I might 
> try
> here. There are two database tables that I'm interested which keep track of
> contacts.
>
> One table "contacts" simply keeps contact_id which is unique
>
> A second table "contact_name" keeps track of key value pairs for the contacts:
>
> CREATE TABLE contacts_name
> (contacts_name_id INTEGER PRIMARY KEY,contacts_id
>  REFERENCES contacts(contacts_id),
>  field_name TEXT,
>  value TEXT NOT NULL);
> CREATE INDEX contacts_name_contacts_id ON contacts_name(contacts_id);
>
>
> So in Contacts table I might have a few id's
> 1
> 2
>
> And in contacts_name I might have a few key value pairs:
>
> ID  field_namevalue
> 1   Name  Tom
> 1  Surname  Jones
> 2   Name  Fred
> 2   Surname Flintstone
>
> I didn't design this system and it don't seem ideal to me, coming from C, but
> I'll have to get on with it. I decided that to make things simpler for my C I
> could create a temporary table and populate it with contact_id, name and
> Surname which is all that I'm interested in.
>
> So I created a new table with:
>
> create temporary table if not exists contacts_tmp
> (contacts_id REFERENCES contacts(contacts_id),
> Name TEXT, Surname TEXT)
>
> That's a database table I could work with ;-) Now I have to populate it with
> the data from the existing contacts_name table so I get the contacts_id and
> Name inserted with:
>
> INSERT INTO contacts_tmp (contacts_id, name)
> SELECT contacts_id, value FROM contacts_name
> WHERE field_name="Name"
>
>
> The above statement works insofar as it populate id and name but I can't get
> surname into my table. I've tried to write a second insert statement to pull
> out the Surname tag from contacts_name but I just can't get it. I'm trying
> something along the lines of:
>
> INSERT INTO contacts_tmp (Surname)
> SELECT contacts_name.value FROM contacts_name  where
> contacts_name.field_name="Surname"
> JOIN contacts_name ON contacts_tmp.contacts_id = contacts_name.contacts_id
>
> Appologies for the long first post but I can't find a solution. Any advice,
> apart from redesigning the old tables would be greatefully received.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
select
 c.contacts_id,
 n.value as Name,
 s.value as Surname
from contacts c
left join contacts_name n
on c.contacts_id=n.contacts_id and n.field_name='Name'
left join contacts_name s
on c.contacts_id=s.contacts_id and s.field_name='Surname'



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SQL question?

[luuk34]

On 17-11-10 10:00, luuk34 wrote:
> On 17-11-10 09:58, Bart Smissaert wrote:
>> What do you suggest should be the full SQL then?
>>
> select  t1.patient_id
> fromtable1 t1
> join(
> select  table1.address,
> min( table1.date_of_birth ) as date_of_birth
> fromtable1
> group bytable1.address
> )
> as  t2
> ON t2.address = t1.address
> and t2.date_of_birth = t1.date_of_birth
>
i forgot the link:
http://www.sqlite.org/syntaxdiagrams.html#join-constraint
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SQL question?

[luuk34]

On 17-11-10 09:58, Bart Smissaert wrote:
> What do you suggest should be the full SQL then?
>
select  t1.patient_id
fromtable1 t1
join(
 select  table1.address,
 min( table1.date_of_birth ) as date_of_birth
 fromtable1
 group bytable1.address
 )
as  t2
ON t2.address = t1.address
and t2.date_of_birth = t1.date_of_birth

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SQL question?

[luuk34]

On 17-11-10 00:17, Petite Abeille wrote:
> select  t1.patient_id
> fromtable1 t1
> join(
>  select  table1.address,
>  min( table1.date_of_birth ) as date_of_birth
>  fromtable1
>  group bytable1.address
>  )
> as  t2
> joint2.address = t1.address

'join' should be:

ONt2.address = t1.address


> and t2.date_of_birth = t1.date_of_birth

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to optimize a multi-condition query

[luuk34]

  On 15-10-10 14:34, Black, Michael (IS) wrote:
> I love simple examples like this can help people with understanding 
> things...so I tried this which I thought would do what Hilmar wants...but 
> alaswhat concept am I missing?
>
> SQLite version 3.7.2
> sqlite>  create table c(achr char,bchr char);
> sqlite>  create index c_chr on c(achr,bchr);
> sqlite>  explain query plan select achr,bchr from c where achr=bchr;
> 0|0|TABLE c
>
> Why no use of the index in this case?
>

because there are no records in the database,
so its quicker to read just all records,
than to read all record in the order of the index...

-- 
Luuk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tricky grouping query

[luuk34]

  On 01-10-10 22:30, Igor Tandetnik wrote:
> Andy Chambers  wrote:
>> Given the following
>>
>> create table events (
>> id,
>> date,
>> status
>> );
>> insert into events values ('001','a','N');
>> insert into events values ('001','b','N');
>> insert into events values ('001','c','Y');
>> insert into events values ('001','d','N');
>> insert into events values ('001','e','Y');
>> insert into events values ('001','f','Y');
>> insert into events values ('001','g','N');
>>
>> Is it possible, using plain SQL, to derive the following
>>
>> 001,c,d
>> 001,e,g
>>
>> i.e. an "N" in the third column means event "001" has stopped, and a
>> "Y" means it
>> has started back up again.  Note that because the status immediately
>> preceding "f"
>> is also "Y", there is no corresponding row in the output
> select Start.id, Start.date, Stop.date
> from events Start join events Stop on (Start.id = Stop.id and Start.date<  
> Stop.date)
> where Start.status = 'Y' and Stop.status = 'N' and not exists
>(select 1 from events Middle
> where Middle.date>  Start.date and Middle.date<  Stop.date and 
> Middle.status = 'N');
>
> Igor Tandetnik
>
your query returns:
001|c|d
001|e|g
001|f|g

so i tried, and come up with this:
select e.id, e.date, a.date
from events e
left join events a on e.id=a.id and e.datehttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query to compare two sqlite databases

[luuk34]

  On 24-09-10 21:06, Rich Shepard wrote:
> On Fri, 24 Sep 2010, John Reed wrote:
>
>> I compare an application every few days which has sqlite as it's client
>> database. I look at the content and check whether documents have made it
>> into the application after it has been built. I also check the metadata in
>> the sqlite client database for changes. So, I am constantly comparing the
>> last database with the newer database. Both databases have exactly the
>> same tables, with only the data being changed in most of the 51 tables.The
>> largest table has about 3,700,000 rows. Most other tables have much less
>> rows in them. Could someone suggest an sql query to find the difference in
>> the same table (ta) for both the last database (db1) and the newer
>> database (db2)? I can use SQLiteSpy to connect and attach to the
>> databases.
> You'll want to tune the syntax, but try something like:
>
> SELECT colA FROM db1 WHERE (NOT EXIST colA IN db2);
>
> The idea is to match rows in each table and where the equivalent row in db1
> is not in db2, add that to the results table.
>
> Rich
you mean something like:
select  id1, id2 from callprog a where id1 not in (select b.id1 FROM 
callprog b where b.id1=a.id1 );

But what is there is more than 1 column?
it will grow in complexity when you have a lot of columns.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users