compare two table in mysql

[inaba]

hi all,

 

In mysql,is it possible to compare two table data (which have exactly similar table structure).

 

The tables initially contain same data.But one table may be updated by the user.

The purpose is to find which of the rows are updated in that table.

 

 

can anybody help.

[fenway]

MyISAM has checksums....

[suttercain]

Hi Fenway and to anyone else who may bea ble to assist. I have the same question as the original. I did a search for checksum and mysql but so far have been unable to gather an explanation of how to do this.

 

I have two tables, each with one column that are identical that need to be compared. Table A has 200 records and Table B has 260 records. I need to basically subtract the 200 records that appear in table B and leave only the 60 standing. That way I know what data has been added in those 60 records.

 

Can anyone give a direction on how to do this? I think I can loop through the data using php and output, but is there an easier way?

 

Thanks in advance.

 

SC

[fenway]

Depends how the records in A and B are related... a LEFT JOIN may be enough.

[suttercain]

Hi Fenway,

 

I was able to do it using this

 

$sql = "SELECT a0400502.* FROM a0400502
	LEFT JOIN a04005 ON a0400502.eng_fam = a04005.eng_fam
	WHERE a04005.eng_fam IS NULL
	GROUP BY a0400502.eng_fam";

 

With that working correctly do you know a way I could modify the SELECT statement to compare 3 or 4 tables at one time? Each table has 1 column and I need to be able to assign dates to ones that are being added..

 

Example:

 

Table 1

AAA

AAB

AAC

 

Table 2

AAA

AAB

AAC

BBB

BBA

BBC

 

Table 3

AAA

AAB

AAC

BBB

BBA

BBC

CCC

CCA

CCB

 

I would need to be able to assign dates like so...

Table 3

AAA 2001-01-01

AAB 2001-01-01

AAC 2001-01-01

BBB 2002-01-01

BBA 2002-01-01

BBC 2002-01-01

CCC 2003-01-01

CCA 2003-01-01

CCB 2003-01-01

 

This is easy with two tables... but with three, .like above, it's hard.

[fenway]

Compare to each other? To a single table?

[suttercain]

Ideally I would like to be able to compare numerous tables, A, B, C, D, etc. And see which records were added over time. The evolution of the tables...

 

A has 5 records, B has 10 (so five have been added but which 5?), C has 25 records (20 have been added since A, which 20. 15 have been added since B, which 15?)

 

Thanks again.

[fenway]

Well, it's no different than the above.