Compare 2 tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ethoemmes@blueyonder.co.uk - 08 Mar 2005 16:26 GMT

I have 2 tables (tblSAPDetails & tblVendorDetails) they both have
Vendor as the PK. I would like to run a query which would highligh the
differences between the 2 tables on 2 fields (ABC and Industry)

Eg:

TblSAPDetails:
Vendor ABC Industry
1004010 A MK

TblVendorDetails:
Vendor ABC Industry
1004010 B MK

Should somehow return the difference between the 2 tables.

Can anyone point me in the right direction?

TIA

Reply to this Message

Keith - 08 Mar 2005 16:32 GMT

> I have 2 tables (tblSAPDetails & tblVendorDetails) they both have
> Vendor as the PK. I would like to run a query which would highligh the
[quoted text clipped - 11 lines]
>
> Should somehow return the difference between the 2 tables.

Try concatonating the 3 fields for TblSAPDetails in one query and
concatonating the fields for TblVendorDetails in another. If I'm
understanding you correctly, you could then include them in a third query
with an outer join on the concatonated fields and test for nulls.

Regards,
Keith.
www.keithwilby.com

Reply to this Message

Alan Webb - 08 Mar 2005 17:36 GMT

Keith,
Why not do a calculated join on the two columns, for example:
SELECT VENDOR_OLD.NAME AS OLD_VENDOR_NAME, VENDOR_NEW.NAME AS
NEW_VENDOR_NAME
FROM tblSAPDetails as VENDOR_OLD, tblVendorDetails AS VENDOR_NEW
WHERE VENDOR_OLD.ABC <> VENDOR_NEW.ABC
AND VENDOR_OLD.INDUSTRY <> VENDOR_NEW.INDUSTRY

This will give you anything that does not have a matching ABC and Industry.

>> I have 2 tables (tblSAPDetails & tblVendorDetails) they both have
>> Vendor as the PK. I would like to run a query which would highligh the
[quoted text clipped - 20 lines]
> Keith.
> www.keithwilby.com

Reply to this Message