How do matching invoice numbers connect the wrong supplier?

Prepare the diagnostic input

Invoice number 001 is unique only within a supplier. Matching it without the supplier creates plausible but false ledger pairs. Use both identity fields; amount is a value to verify after identity matching, not a substitute for the missing supplier key.

Use a fresh SQLite test database for the complete setup below. Every record is fictional; quantities are units and input monetary columns ending in cents use integer currency cents. Calculated averages may include fractions of a cent. Input previews include every table in this small fixture.

CREATE TABLE intake(supplier TEXT,invoice_no TEXT,amount_cents INTEGER);
CREATE TABLE ledger(supplier TEXT,invoice_no TEXT,amount_cents INTEGER,PRIMARY KEY(supplier,invoice_no));
INSERT INTO intake VALUES ('A','001',10000),('B','001',20000);
INSERT INTO ledger VALUES ('A','001',10000),('B','001',20000);

Reproduce the misleading result and correct it

SELECT i.supplier,i.amount_cents AS source_cents,l.supplier AS matched_supplier,l.amount_cents AS target_cents
FROM intake i JOIN ledger l ON l.invoice_no=i.invoice_no ORDER BY i.supplier,l.supplier;

SELECT i.supplier,i.amount_cents AS source_cents,l.supplier AS matched_supplier,l.amount_cents AS target_cents
FROM intake i JOIN ledger l ON l.invoice_no=i.invoice_no AND l.supplier=i.supplier ORDER BY i.supplier;

The wrong join creates four rows, including A’s HK$100 against B’s HK$200 and the reverse. The corrected match produces two legitimate pairs. This differs from a missing-customer report: both ledger invoices exist, but an incomplete key invents relationships.

Check the boundary before applying the fix

Add supplier C with invoice 001 only in intake. It must not borrow A or B’s ledger entry. The inner-join example omits it; use a separate unmatched-record review before claiming every intake row reconciled.

For the boundary test, recreate the complete setup in another fresh synthetic database, apply only the change below, then rerun the corrected query. Do not apply a test mutation to a live system.

INSERT INTO intake VALUES ('C','001',30000);

The supplier identity must already be mapped consistently. Renamed suppliers, credit notes and invoice-number corrections need their own identity policy.

The displayed bad, corrected and boundary results were executed in SQLite and checked against separately authored expected rows. This demonstrates the stated fixture only; it does not test other database engines, production performance or your live schema.