This concise and powerful SQL statement returns out-of-sync duplicated data. On one hand, we could say that the tables should be normalized and therefore the data would never be out-of-sync. In this case, if it was normalized, 1425 records would contain unfixable errors. I was fortunate to have some duplication. The errors were introduced over 3 years using a correct business rule that we discovered (during an audit review) should now be different.
select x.inventory_id, p.caci_po, (select acq_doc_num from inventory where inventory_id = x.inventory_id ) from inventory_po_xwalk x inner join purchase_orders p on x.po_id = p.id and p.caci_po != (select acq_doc_num from inventory where inventory_id = x.inventory_id)
There is a better way to obtain protection against business rules that change data: using a trigger that records each change in an audit table. However, its much harder to extract the desired changes from an audit table than it is to use a set of 3 tables with the master-xwalk-duplicate pattern.
