Comparing 2 tables

Hi All

I need to compare 2 tables ( I need to eliminate 2 columns in the
table while comparing, they are dates and userids which wil be
different. )

Example:

TableA( colA, colB, colC, colD, colE)
TableB( colA, colB, colC, colD, colE)

I need to compare TableA and TableB , for me they are equal if
contents of colA, colB, colC are same , need not compare colD and
colE.

I write jdbc program which reads tables and compare row by row, just I
think it is laborious. Is there a better way of doing this. Please let
me know.

Thanks

m wrote:
> Hi All
>
> I need to compare 2 tables ( I need to eliminate 2 columns in the
> table while comparing, they are dates and userids which wil be
> different. )
>
> Example:
>
> TableA( colA, colB, colC, colD, colE)
> TableB( colA, colB, colC, colD, colE)
>
> I need to compare TableA and TableB , for me they are equal if
> contents of colA, colB, colC are same , need not compare colD and
> colE.
>
> I write jdbc program which reads tables and compare row by row, just I
> think it is laborious. Is there a better way of doing this. Please let
> me know.

You may want to consider a left join between the two tables, along the
lines of

select TableA.*,TableB.* from TableA left join TableB on
(TableA.colA = TableB.colA and TableA.colB = TableB.colB and
TableA.colC = TableB.colC)
where TableB.colD is null or TableB.colE is null;

This will show you which rows in TableA are not equal (by your criteria)
to rows in TableB.

You probably need to repeat the query exchanging TableA and TableB to
find rows in TableB which are not equal to rows in TableA.

This assumes, of course, that the triples (colA,colB,colC) are unique in
each table i.e. there is only one row in TableA with any given
combination of (colA,colB,colC) and likewise with TableB.

David Harper
Cambridge, England