Re: [sqlite] Comparing two tables column by column

[ibrahim]

On 30.07.2013 14:49, fnoyan...@yahoo.com wrote:

Yes, it is slow actually. But I could not do it with SQL. I wish I could do 
more with SQL than code, which would give a good performance improvement.

 From overall application point of view, I may be considering using threads to 
read from database, so that the performance will be improved a bit.

On 30/07/2013, at 4:26 PM, "Stadin, Benjamin" 
<benjamin.sta...@heidelberg-mobil.com> wrote:


That can work, if performance is of no concern. Otherwise it will become 
miserably slow.


Von: Fehmi Noyan ISI <fnoyan...@yahoo.com>
Antworten an: Fehmi Noyan ISI <fnoyan...@yahoo.com>
Datum: Dienstag, 30. Juli 2013 01:54
An: Benjamin Stadin <benjamin.sta...@heidelberg-mobil.com>, General Discussion of 
SQLite Database <sqlite-users@sqlite.org>
Betreff: Re: [sqlite] Comparing two tables column by column

The approach I am using to compare tableA_old and tableA_new is;

typedef struct container_t {
// a structure to pass parameters into callbacks
} container;

static int callback_2(){
 // compare two values in the resulting table if they are different
 // write them into a resulting file
}

static int callback_1(){
 for (each column){
 char sql[256];
 sprintf(sql,"select %s from tableA_old where 
pkey=%s",columnName,primarykey);
 sqlite3_exec(db,sql,callback_2,,)
 }
}

int main(){
  // import CS file into DB
  // for each pair of files (each file is a table and files have
  // the same column names in the same order). Actually, these are
  // records for different dates
  char sql[256] = "select * from tableA_new;";
  container c;
  sqlite3_exec(db,sql,callback_1,(void*)c,null)
  
}

From: "Stadin, Benjamin" <benjamin.sta...@heidelberg-mobil.com>
To: Fehmi Noyan ISI <fnoyan...@yahoo.com>; General Discussion of SQLite Database 
<sqlite-users@sqlite.org>
Sent: Tuesday, July 30, 2013 9:00 AM
Subject: Re: [sqlite] Comparing two tables column by column

If you like ruby, I have another idea to get you going (maybe without
needing to write much code):

- Use a registered function to SQLite to create MD5 or SHA1 keys for rows
in the table. Here is a ruby snippet that registers a SHA1 function:
http://www.copiousfreetime.org/articles/2009/01/10/writing-sql-functions-in
-ruby.html
- Join both tables by your primary key and select those where the SHA keys
don't match
- go threw each column programmatically in your ruby code and simply
compare column values with each other

If you then need performance and get by some means a trigger for your
newly inserted records, add a sha1 field to all of your tables and
precompute and index the sha1 for each table.

Benjamin Stadin

Am 30.07.13 01:07 schrieb "Fehmi Noyan ISI" unter <fnoyan...@yahoo.com>:


EXCEPT query gave the different rows in tables, but what I am after is
the different values for existing records.

The column names are exactly the same, however the number of rows may
differ (with most of the records are the same).



From: Simon Slavin <slav...@bigfraud.org>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Monday, July 29, 2013 9:10 PM
Subject: Re: [sqlite] Comparing two tables column by column



On 29 Jul 2013, at 12:36pm, Fabian Klebert
<f.kleb...@klebert-engineering.de> wrote:


Wouldn't

SELECT * FROM table1
EXCEPT
SELECT * FROM table2

solve this problem?
I think it does for the example provided. Not sure if it would work in
real-world environment.

There are two elements: making sure the same rows are present, and making
sure the contents of the rows match.  I would probably use EXCEPT
commands to find out entries in one database which weren't in the other,
then use INTERSECT command to check that the fields in the rows which
were in both.  But that's just at first glance.

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



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
a) Store your table backups ordered by a primary key (not rowid but 
integer primary key).
b) Make a whole table select ordered by the same primary key for the 
current table.

c) Merge both tables.

This will be faster cause you won't need to trace through the BTREE 
(B+TREE) for each recordset.


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

Re: [sqlite] Comparing two tables column by column

[fnoyanisi]

Yes, it is slow actually. But I could not do it with SQL. I wish I could do 
more with SQL than code, which would give a good performance improvement.

>From overall application point of view, I may be considering using threads to 
>read from database, so that the performance will be improved a bit.

On 30/07/2013, at 4:26 PM, "Stadin, Benjamin" 
<benjamin.sta...@heidelberg-mobil.com> wrote:

> That can work, if performance is of no concern. Otherwise it will become 
> miserably slow.
> 
> 
> Von: Fehmi Noyan ISI <fnoyan...@yahoo.com>
> Antworten an: Fehmi Noyan ISI <fnoyan...@yahoo.com>
> Datum: Dienstag, 30. Juli 2013 01:54
> An: Benjamin Stadin <benjamin.sta...@heidelberg-mobil.com>, General 
> Discussion of SQLite Database <sqlite-users@sqlite.org>
> Betreff: Re: [sqlite] Comparing two tables column by column
> 
> The approach I am using to compare tableA_old and tableA_new is;
> 
> typedef struct container_t {
> // a structure to pass parameters into callbacks
> } container;
> 
> static int callback_2(){
> // compare two values in the resulting table if they are different 
> // write them into a resulting file
> }
> 
> static int callback_1(){
> for (each column){
> char sql[256];
> sprintf(sql,"select %s from tableA_old where 
> pkey=%s",columnName,primarykey);
> sqlite3_exec(db,sql,callback_2,,)
> }
> }
> 
> int main(){
>  // import CS file into DB
>  // for each pair of files (each file is a table and files have
>  // the same column names in the same order). Actually, these are 
>  // records for different dates
>  char sql[256] = "select * from tableA_new;";
>  container c;
>  sqlite3_exec(db,sql,callback_1,(void*)c,null)
>  
> }
> From: "Stadin, Benjamin" <benjamin.sta...@heidelberg-mobil.com>
> To: Fehmi Noyan ISI <fnoyan...@yahoo.com>; General Discussion of SQLite 
> Database <sqlite-users@sqlite.org> 
> Sent: Tuesday, July 30, 2013 9:00 AM
> Subject: Re: [sqlite] Comparing two tables column by column
> 
> If you like ruby, I have another idea to get you going (maybe without
> needing to write much code):
> 
> - Use a registered function to SQLite to create MD5 or SHA1 keys for rows
> in the table. Here is a ruby snippet that registers a SHA1 function:
> http://www.copiousfreetime.org/articles/2009/01/10/writing-sql-functions-in
> -ruby.html
> - Join both tables by your primary key and select those where the SHA keys
> don't match
> - go threw each column programmatically in your ruby code and simply
> compare column values with each other
> 
> If you then need performance and get by some means a trigger for your
> newly inserted records, add a sha1 field to all of your tables and
> precompute and index the sha1 for each table.
> 
> Benjamin Stadin
> 
> Am 30.07.13 01:07 schrieb "Fehmi Noyan ISI" unter <fnoyan...@yahoo.com>:
> 
> >EXCEPT query gave the different rows in tables, but what I am after is
> >the different values for existing records.
> >
> >The column names are exactly the same, however the number of rows may
> >differ (with most of the records are the same).
> >
> >
> >
> > From: Simon Slavin <slav...@bigfraud.org>
> >To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> >Sent: Monday, July 29, 2013 9:10 PM
> >Subject: Re: [sqlite] Comparing two tables column by column
> > 
> >
> >
> >On 29 Jul 2013, at 12:36pm, Fabian Klebert
> ><f.kleb...@klebert-engineering.de> wrote:
> >
> >> Wouldn't 
> >> 
> >> SELECT * FROM table1
> >> EXCEPT
> >> SELECT * FROM table2
> >> 
> >> solve this problem?
> >> I think it does for the example provided. Not sure if it would work in
> >>real-world environment.
> >
> >There are two elements: making sure the same rows are present, and making
> >sure the contents of the rows match.  I would probably use EXCEPT
> >commands to find out entries in one database which weren't in the other,
> >then use INTERSECT command to check that the fields in the rows which
> >were in both.  But that's just at first glance.
> >
> >Simon.
> >___
> >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
> 
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Comparing two tables column by column

[Stadin, Benjamin]

That can work, if performance is of no concern. Otherwise it will become 
miserably slow.


Von: Fehmi Noyan ISI <fnoyan...@yahoo.com<mailto:fnoyan...@yahoo.com>>
Antworten an: Fehmi Noyan ISI <fnoyan...@yahoo.com<mailto:fnoyan...@yahoo.com>>
Datum: Dienstag, 30. Juli 2013 01:54
An: Benjamin Stadin 
<benjamin.sta...@heidelberg-mobil.com<mailto:benjamin.sta...@heidelberg-mobil.com>>,
 General Discussion of SQLite Database 
<sqlite-users@sqlite.org<mailto:sqlite-users@sqlite.org>>
Betreff: Re: [sqlite] Comparing two tables column by column

The approach I am using to compare tableA_old and tableA_new is;

typedef struct container_t {
// a structure to pass parameters into callbacks
} container;

static int callback_2(){
// compare two values in the resulting table if they are different
// write them into a resulting file
}

static int callback_1(){
for (each column){
char sql[256];
sprintf(sql,"select %s from tableA_old where 
pkey=%s",columnName,primarykey);
sqlite3_exec(db,sql,callback_2,,)
}
}

int main(){
 // import CS file into DB
 // for each pair of files (each file is a table and files have
 // the same column names in the same order). Actually, these are
 // records for different dates
 char sql[256] = "select * from tableA_new;";
 container c;
 sqlite3_exec(db,sql,callback_1,(void*)c,null)

}

From: "Stadin, Benjamin" 
<benjamin.sta...@heidelberg-mobil.com<mailto:benjamin.sta...@heidelberg-mobil.com>>
To: Fehmi Noyan ISI <fnoyan...@yahoo.com<mailto:fnoyan...@yahoo.com>>; General 
Discussion of SQLite Database 
<sqlite-users@sqlite.org<mailto:sqlite-users@sqlite.org>>
Sent: Tuesday, July 30, 2013 9:00 AM
Subject: Re: [sqlite] Comparing two tables column by column

If you like ruby, I have another idea to get you going (maybe without
needing to write much code):

- Use a registered function to SQLite to create MD5 or SHA1 keys for rows
in the table. Here is a ruby snippet that registers a SHA1 function:
http://www.copiousfreetime.org/articles/2009/01/10/writing-sql-functions-in
-ruby.html
- Join both tables by your primary key and select those where the SHA keys
don't match
- go threw each column programmatically in your ruby code and simply
compare column values with each other

If you then need performance and get by some means a trigger for your
newly inserted records, add a sha1 field to all of your tables and
precompute and index the sha1 for each table.

Benjamin Stadin

Am 30.07.13 01:07 schrieb "Fehmi Noyan ISI" unter 
<fnoyan...@yahoo.com<mailto:fnoyan...@yahoo.com>>:

>EXCEPT query gave the different rows in tables, but what I am after is
>the different values for existing records.
>
>The column names are exactly the same, however the number of rows may
>differ (with most of the records are the same).
>
>
>
> From: Simon Slavin <slav...@bigfraud.org<mailto:slav...@bigfraud.org>>
>To: General Discussion of SQLite Database 
><sqlite-users@sqlite.org<mailto:sqlite-users@sqlite.org>>
>Sent: Monday, July 29, 2013 9:10 PM
>Subject: Re: [sqlite] Comparing two tables column by column
>
>
>
>On 29 Jul 2013, at 12:36pm, Fabian Klebert
><f.kleb...@klebert-engineering.de<mailto:f.kleb...@klebert-engineering.de>> 
>wrote:
>
>> Wouldn't
>>
>> SELECT * FROM table1
>> EXCEPT
>> SELECT * FROM table2
>>
>> solve this problem?
>> I think it does for the example provided. Not sure if it would work in
>>real-world environment.
>
>There are two elements: making sure the same rows are present, and making
>sure the contents of the rows match.  I would probably use EXCEPT
>commands to find out entries in one database which weren't in the other,
>then use INTERSECT command to check that the fields in the rows which
>were in both.  But that's just at first glance.
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org<mailto:sqlite-users@sqlite.org>
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org<mailto: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] Comparing two tables column by column

[Fehmi Noyan ISI]

The approach I am using to compare tableA_old and tableA_new is;

typedef struct container_t {
// a structure to pass parameters into callbacks
} container;

static int callback_2(){
    // compare two values in the resulting table if they are different 
    // write them into a resulting file
}


static int callback_1(){
    for (each column){
    char sql[256];
        sprintf(sql,"select %s from tableA_old where 
pkey=%s",columnName,primarykey);

        sqlite3_exec(db,sql,callback_2,,)
    }
}

int main(){
 // import CS file into DB
 //for each pair of files (each file is a table and files have
 // the same column names in the same order). Actually, these are 

 // records for different dates
 char sql[256] = "select * from tableA_new;";
 container c;

 sqlite3_exec(db,sql,callback_1,(void*)c,null)

 

}



 From: "Stadin, Benjamin" <benjamin.sta...@heidelberg-mobil.com>
To: Fehmi Noyan ISI <fnoyan...@yahoo.com>; General Discussion of SQLite 
Database <sqlite-users@sqlite.org> 
Sent: Tuesday, July 30, 2013 9:00 AM
Subject: Re: [sqlite] Comparing two tables column by column
 

If you like ruby, I have another idea to get you going (maybe without
needing to write much code):

- Use a registered function to SQLite to create MD5 or SHA1 keys for rows
in the table. Here is a ruby snippet that registers a SHA1 function:
http://www.copiousfreetime.org/articles/2009/01/10/writing-sql-functions-in
-ruby.html
- Join both tables by your primary key and select those where the SHA keys
don't match
- go threw each column programmatically in your ruby code and simply
compare column values with each other

If you then need performance and get by some means a trigger for your
newly inserted records, add a sha1 field to all of your tables and
precompute and index the sha1 for each table.

Benjamin Stadin

Am 30.07.13 01:07 schrieb "Fehmi Noyan ISI" unter <fnoyan...@yahoo.com>:

>EXCEPT query gave the different rows in tables, but what I am after is
>the different values for existing records.
>
>The column names are exactly the same, however the number of rows may
>differ (with most of the records are the same).
>
>
>
> From: Simon Slavin <slav...@bigfraud.org>
>To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
>Sent: Monday, July 29, 2013 9:10 PM
>Subject: Re: [sqlite] Comparing two tables column by column
> 
>
>
>On 29 Jul 2013, at 12:36pm, Fabian Klebert
><f.kleb...@klebert-engineering.de> wrote:
>
>> Wouldn't 
>> 
>> SELECT * FROM table1
>> EXCEPT
>> SELECT * FROM table2
>> 
>> solve this problem?
>> I think it does for the example provided. Not sure if it would work in
>>real-world environment.
>
>There are two elements: making sure the same rows are present, and making
>sure the contents of the rows match.  I would probably use EXCEPT
>commands to find out entries in one database which weren't in the other,
>then use INTERSECT command to check that the fields in the rows which
>were in both.  But that's just at first glance.
>
>Simon.
>___
>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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Comparing two tables column by column

[Stadin, Benjamin]

If you like ruby, I have another idea to get you going (maybe without
needing to write much code):

- Use a registered function to SQLite to create MD5 or SHA1 keys for rows
in the table. Here is a ruby snippet that registers a SHA1 function:
http://www.copiousfreetime.org/articles/2009/01/10/writing-sql-functions-in
-ruby.html
- Join both tables by your primary key and select those where the SHA keys
don't match
- go threw each column programmatically in your ruby code and simply
compare column values with each other

If you then need performance and get by some means a trigger for your
newly inserted records, add a sha1 field to all of your tables and
precompute and index the sha1 for each table.

Benjamin Stadin

Am 30.07.13 01:07 schrieb "Fehmi Noyan ISI" unter <fnoyan...@yahoo.com>:

>EXCEPT query gave the different rows in tables, but what I am after is
>the different values for existing records.
>
>The column names are exactly the same, however the number of rows may
>differ (with most of the records are the same).
>
>
>
> From: Simon Slavin <slav...@bigfraud.org>
>To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
>Sent: Monday, July 29, 2013 9:10 PM
>Subject: Re: [sqlite] Comparing two tables column by column
> 
>
>
>On 29 Jul 2013, at 12:36pm, Fabian Klebert
><f.kleb...@klebert-engineering.de> wrote:
>
>> Wouldn't 
>> 
>> SELECT * FROM table1
>> EXCEPT
>> SELECT * FROM table2
>> 
>> solve this problem?
>> I think it does for the example provided. Not sure if it would work in
>>real-world environment.
>
>There are two elements: making sure the same rows are present, and making
>sure the contents of the rows match.  I would probably use EXCEPT
>commands to find out entries in one database which weren't in the other,
>then use INTERSECT command to check that the fields in the rows which
>were in both.  But that's just at first glance.
>
>Simon.
>___
>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

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

Re: [sqlite] Comparing two tables column by column

[Fehmi Noyan ISI]

EXCEPT query gave the different rows in tables, but what I am after is the 
different values for existing records.

The column names are exactly the same, however the number of rows may differ 
(with most of the records are the same).



 From: Simon Slavin <slav...@bigfraud.org>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org> 
Sent: Monday, July 29, 2013 9:10 PM
Subject: Re: [sqlite] Comparing two tables column by column
 


On 29 Jul 2013, at 12:36pm, Fabian Klebert <f.kleb...@klebert-engineering.de> 
wrote:

> Wouldn't 
> 
> SELECT * FROM table1
> EXCEPT
> SELECT * FROM table2
> 
> solve this problem?
> I think it does for the example provided. Not sure if it would work in 
> real-world environment.

There are two elements: making sure the same rows are present, and making sure 
the contents of the rows match.  I would probably use EXCEPT commands to find 
out entries in one database which weren't in the other, then use INTERSECT 
command to check that the fields in the rows which were in both.  But that's 
just at first glance.

Simon.
___
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] Comparing two tables column by column

[Simon Slavin]

On 29 Jul 2013, at 12:36pm, Fabian Klebert  
wrote:

> Wouldn't 
> 
> SELECT * FROM table1
> EXCEPT
> SELECT * FROM table2
> 
> solve this problem?
> I think it does for the example provided. Not sure if it would work in 
> real-world environment.

There are two elements: making sure the same rows are present, and making sure 
the contents of the rows match.  I would probably use EXCEPT commands to find 
out entries in one database which weren't in the other, then use INTERSECT 
command to check that the fields in the rows which were in both.  But that's 
just at first glance.

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

Re: [sqlite] Comparing two tables column by column

[Fabian Klebert]

Wouldn't 

SELECT * FROM table1
EXCEPT
SELECT * FROM table2

solve this problem?
I think it does for the example provided. Not sure if it would work in 
real-world environment.


-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
Im Auftrag von Stephen Chrzanowski
Gesendet: Montag, 29. Juli 2013 13:01
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Comparing two tables column by column

To be fair to me, the example had the same column names.  If the two tables 
have the same column names, then having a bit of extra code to tag on the 
column name + "_1" might have worked.  As my first reply answered, untested. ;)


On Mon, Jul 29, 2013 at 6:46 AM, Clemens Ladisch <clem...@ladisch.de> wrote:

> Simon Slavin wrote:
> > On 29 Jul 2013, at 4:03am, Fehmi Noyan ISI <fnoyan...@yahoo.com> wrote:
> >> One point I forgot to mention; the number of columns is unknown.
> >
> > There is no way in SQL to say "Give me the contents of all the 
> > columns
> of a row of table in an unambiguous format.".
>
> Well, just "give me" could be done with "SELECT *", but it is almost 
> impossible to compare such columns in SQL.
>
> If the tables have the same number _and_ names of columns, it would be 
> possible to use a NATURAL JOIN, and combine this with an outer join to 
> get non-matching records:
>
> SELECT table1.* FROM table1 NATURAL LEFT JOIN table2 WHERE 
> table2.rowid IS NULL
>
> This will return those records from table1 that do not have a matching 
> record in table2.  For the other direction, run the same query with 
> "table1" and "table2" exchanged.
>
>
> Regards,
> Clemens
> ___
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Comparing two tables column by column

[Stephen Chrzanowski]

To be fair to me, the example had the same column names.  If the two tables
have the same column names, then having a bit of extra code to tag on the
column name + "_1" might have worked.  As my first reply answered,
untested. ;)


On Mon, Jul 29, 2013 at 6:46 AM, Clemens Ladisch  wrote:

> Simon Slavin wrote:
> > On 29 Jul 2013, at 4:03am, Fehmi Noyan ISI  wrote:
> >> One point I forgot to mention; the number of columns is unknown.
> >
> > There is no way in SQL to say "Give me the contents of all the columns
> of a row of table in an unambiguous format.".
>
> Well, just "give me" could be done with "SELECT *", but it is almost
> impossible to compare such columns in SQL.
>
> If the tables have the same number _and_ names of columns, it would be
> possible to use a NATURAL JOIN, and combine this with an outer join to
> get non-matching records:
>
> SELECT table1.* FROM table1 NATURAL LEFT JOIN table2 WHERE
> table2.rowid IS NULL
>
> This will return those records from table1 that do not have a matching
> record in table2.  For the other direction, run the same query with
> "table1" and "table2" exchanged.
>
>
> Regards,
> Clemens
> ___
> 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] Comparing two tables column by column

[Clemens Ladisch]

Simon Slavin wrote:
> On 29 Jul 2013, at 4:03am, Fehmi Noyan ISI  wrote:
>> One point I forgot to mention; the number of columns is unknown.
>
> There is no way in SQL to say "Give me the contents of all the columns of a 
> row of table in an unambiguous format.".

Well, just "give me" could be done with "SELECT *", but it is almost
impossible to compare such columns in SQL.

If the tables have the same number _and_ names of columns, it would be
possible to use a NATURAL JOIN, and combine this with an outer join to
get non-matching records:

SELECT table1.* FROM table1 NATURAL LEFT JOIN table2 WHERE table2.rowid IS 
NULL

This will return those records from table1 that do not have a matching
record in table2.  For the other direction, run the same query with
"table1" and "table2" exchanged.


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

Re: [sqlite] Comparing two tables column by column

[fnoyanisi]

Yes, it turned out that achieving the goal with C code is much simpler than 
using SQL statements (I also take my limited sql knowledge into account)

Now, I'll have two sqlite3_exec() calls, one of which is invoked by first 
call's callback function. This led having some natsy C structs around to pass 
parameters to sqlite3_exec() calls, but I could not find any other approach.

On 29/07/2013, at 7:48 PM, Simon Slavin  wrote:

> 
> On 29 Jul 2013, at 4:03am, Fehmi Noyan ISI  wrote:
> 
>> One point I forgot to mention; the number of columns is unknown.
> 
> There is no way in SQL to say "Give me the contents of all the columns of a 
> row of table in an unambiguous format.".
> 
> It would be possible to write the code you want in SQLite, by using various 
> PRAGMAs to inspect the table format, but this would lead to complicated code 
> which was highly tuned to how SQLite works.
> 
> I suspect you'd be better off writing C code which inspects whatever "SELECT 
> *' returns.
> 
> Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Comparing two tables column by column

[Simon Slavin]

On 29 Jul 2013, at 4:03am, Fehmi Noyan ISI  wrote:

> One point I forgot to mention; the number of columns is unknown.

There is no way in SQL to say "Give me the contents of all the columns of a row 
of table in an unambiguous format.".

It would be possible to write the code you want in SQLite, by using various 
PRAGMAs to inspect the table format, but this would lead to complicated code 
which was highly tuned to how SQLite works.

I suspect you'd be better off writing C code which inspects whatever "SELECT *' 
returns.

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

Re: [sqlite] Comparing two tables column by column

[Fehmi Noyan ISI]

Thanks for the reply...

One point I forgot to mention; the number of columns is unknown... Actually 
there are more than 40-50 tables that needs to be examined.





 From: Stephen Chrzanowski <pontia...@gmail.com>
To: Fehmi Noyan ISI <fnoyan...@yahoo.com>; General Discussion of SQLite 
Database <sqlite-users@sqlite.org> 
Sent: Monday, July 29, 2013 12:31 PM
Subject: Re: [sqlite] Comparing two tables column by column
 


Untested

select table1.col1 as Key, table1.col2 as T1C2, table1.col3 as T1C3, 
table1.col4 as T1C4, table2.col2 as T2C2, table2.col3 as T2C3, table2.col4 as 
T2C4 from Table1 join Table2 on Table1.Col1=Table2.Col2 order by table1.col1





On Sun, Jul 28, 2013 at 10:44 PM, Fehmi Noyan ISI <fnoyan...@yahoo.com> wrote:

Hi,
>
>I would like to compare two tables row by row having same primary keys. The 
>comparison should take each row from TABLE1 and find relevant entry (based on 
>KEY) from TABLE2 and compare value of EACH column.
>
>For the TABLE1 and TABLE2 below, the values of COL4 for KEY3 should be 
>returned (both values).
>
>TABLE1
>
>COL1 COL2 COL3 COL4
>KEY1 X Y Z
>KEY2 A B C
>KEY3 K L M
>
>TABLE2
>
>COL1 COL2 COL3 COL4
>KEY1 X Y Z
>KEY2 A B C
>KEY3 K L D
>
>
>Is there a way to do this via SQL or I will try to do it with C loops (which 
>ends up with nasty code!).
>
>Thanks in advance...
>___
>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] Comparing two tables column by column

[Stephen Chrzanowski]

Untested

select table1.col1 as Key, table1.col2 as T1C2, table1.col3 as T1C3,
table1.col4 as T1C4, table2.col2 as T2C2, table2.col3 as T2C3, table2.col4
as T2C4 from Table1 join Table2 on Table1.Col1=Table2.Col2 order by
table1.col1



On Sun, Jul 28, 2013 at 10:44 PM, Fehmi Noyan ISI wrote:

> Hi,
>
> I would like to compare two tables row by row having same primary keys.
> The comparison should take each row from TABLE1 and find relevant entry
> (based on KEY) from TABLE2 and compare value of EACH column.
>
> For the TABLE1 and TABLE2 below, the values of COL4 for KEY3 should be
> returned (both values).
>
> TABLE1
>
> COL1 COL2 COL3 COL4
> KEY1 X Y Z
> KEY2 A B C
> KEY3 K L M
>
> TABLE2
>
> COL1 COL2 COL3 COL4
> KEY1 X Y Z
> KEY2 A B C
> KEY3 K L D
>
>
> Is there a way to do this via SQL or I will try to do it with C loops
> (which ends up with nasty code!).
>
> Thanks in advance...
> ___
> 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