Joining without a unique identifier

Question

Payments

PaymentRef	Product	PaymentAmount	MerchantRef
P01	ABC	100	MR01
P02	ABC	200	MR02
P03	XYZ	200	MR03

Refunds

ReundRef	Product	PaymentAmount	MerchantRef
R01	ABC	-10	MR04
R02	ABC	-100	MR05

These tables used to be joined on MerchantRef, resulting in a list of payments and refunds. This worked because only single refunds against a product were allowed. Multiple refunds are now allowed, meaning MerchantRef must change for each refund (payment provider requirement) therefore breaking the join. If I change the join use Product, I end up with duplicated rows.

Is there method or some trickery that will allow me to join on Product at all? While Product can exist multiple times, it always refers to the same thing. I don't think there's a way to proceed as we have no way of knowing which refund refers to which payment. The only approach I can think of to SUM() PaymentAmount for all matching Product records, and do the same for refunds, and do it based on total.

SQL isn't my forte at all, but am I right in thinking my only solution here is having a reference between the tables that never changes?

I'm trying to keep changes to a minimum, I never would have started with this design, but here we are.

Answer 1

For your existing data, you are out of luck in terms of reconciling payments and refunds unless you have additional columns that you haven't included or external data (e.g. this is information from the payment processor, but you also have data from the system that initiated the payment which includes both the Merchant Ref and some other data, like customer id).

Moving forward, you probably want a "reference" column in Refunds that you store the MerchantRef from the associated payment. This will allow you to cross-reference (think in terms of how you would do it manually before considering how you would do it technically). Another alternative is to format the MerchantRef so that is it a derivative of the payment. E.g. Payment P01 would use P01 for the MerchantRef and any refunds from it would start with P01, followed by something unique to the refund (e.g. P01R1, P01R2 could be two refunds associated with payment P01).

In terms of working with the data you have, if you are looking at getting totals by, say Product, you can UNION the two tables then aggregate them using GROUP BY. Here is an example:

SELECT
    Product,
    SUM(PaymentAmount) AS TotalPaymentAmount
FROM 
(
    -- There are more columns than strictly needed for the aggregation
    SELECT 'Payment' AS RefType, PaymentRef AS 'Ref', Product, PaymentAmount, MerchantRef FROM Payments
    UNION
    -- Columns must match type
    SELECT 'Refund' AS RefType, PaymentRef AS 'Ref', Product, PaymentAmount, MerchantRef FROM Refunds
) Transactions
GROUP BY Product