Compare data from two different db tables

[jeffmc21]

Hey guys..

I have two database tables. I want to compare fields from tbl_one with fields from tbl_two and the output the resulting similarities.

So, I want to take table_one.field_one and compare it to table_two.field_one. If they match, I'd want to output something like "CORRECT", and if not "INCORRECT". Or even better might be to simply compare the 10 fields from table one to the corresponding ten fields in table two and then output the number of matching fields.

Any help?

[r937]

sounds like this comparison could be handled completely within SQL

how to you match rows?

[RichardDavies]

Does it have to be done in ColdFusion? If not, take a look http://www.red-gate.com/products/SQL...pare/index.htm (They have a 14-day trial).

[angele803]

You could run 2 queries - one for "Table1" and one for "Table2". Then, you could compare the fields returned. Of course, you would have to use some sort of ID so you get a single field value and not the whole column. Then you can just output something based on the results.

So it would be something like:

<cfquery name="GetTable1" datasource="ds">
    SELECT field_one
    FROM table1
    Where id = theidyouwant
</cfquery>

<cfquery name="GetTable2" datasource="ds">
    SELECT field_one
    FROM table2
    Where id = theidyouwant
</cfquery>

<cfif GetTable1.field_one eq GetTable2.field_one>
Correct!
<cfelse>
Incorrect :(
</cfif>