MySQL Left Join Statement Duplicating records

Question

The solution is such:

SELECT
    invoice.invoice_id,
    IFNULL(
        SUM(refund.amount),
        0
    ) AS refund_amount
FROM
    invoice
    INNER JOIN invoice_status ON invoice_status.invoice_status_id = invoice.status_id
    LEFT JOIN reservation ON reservation.reservation_id = invoice.reservation_id
    LEFT JOIN reservation_status ON reservation_status.reservation_status_id = reservation.reservation_status_id
    LEFT JOIN refund_invoice ON refund_invoice.invoice_id = invoice.invoice_id
    LEFT JOIN refund ON refund.refund_id = refund_invoice.refund_id
WHERE
    invoice_status.invoice_status in ('Paid', 'Partially Refunded')
    AND invoice.has_been_dispersed = 0
    AND reservation_status.reservation_status in ('Closed', 'Ended', 'Cancelled')
GROUP BY
    invoice.invoice_id;
    
    
    
    
CREATE TEMPORARY TABLE payments 
SELECT
    invoice.invoice_id,
    IFNULL(
        SUM(payment.amount),
        0
    ) AS payment_amount
FROM
    invoice
    INNER JOIN invoice_status ON invoice_status.invoice_status_id = invoice.status_id
    LEFT JOIN reservation ON reservation.reservation_id = invoice.reservation_id
    LEFT JOIN reservation_status ON reservation_status.reservation_status_id = reservation.reservation_status_id
    LEFT JOIN payment_invoice ON payment_invoice.invoice_id = invoice.invoice_id
    LEFT JOIN payment ON payment.payment_id = payment_invoice.payment_id
WHERE
    invoice_status.invoice_status in ('Paid', 'Partially Refunded')
    AND invoice.has_been_dispersed = 0
    AND reservation_status.reservation_status in ('Closed', 'Ended', 'Cancelled')
GROUP BY
    invoice.invoice_id;
    
    

SELECT
    invoice.invoice_id,
    invoice.company_id,
    invoice_status.invoice_status,
    invoice.total AS invoice_total_amount,
    IFNULL(
        reservation.reservation_id, 'ADHOC INVOICE'
    ) AS reservation_id,
    refund_amount,
    payment_amount,
    invoice.payment_processor_fee,
    invoice.vquip_fee,
    payment_amount - refund_amount AS gross_amount_to_be_dispersed,
    payment_amount - refund_amount 
     - IFNULL(
        SUM(invoice.vquip_fee),
        0
    ) 
    - IFNULL(
        CASE WHEN invoice.charge_customers_payment_processing_fee = 0 THEN IFNULL(
            invoice.payment_processor_fee, 0
        ) WHEN invoice.charge_customers_payment_processing_fee = 1 THEN 0 ELSE 0 END,
        0
    ) AS amount_to_be_dispersed,
    invoice.charge_customers_payment_processing_fee
FROM
    invoice
    INNER JOIN invoice_status ON invoice_status.invoice_status_id = invoice.status_id
    left join payments on payments.invoice_id = invoice.invoice_id
    left join refunds on refunds.invoice_id = invoice.invoice_id
    LEFT JOIN reservation ON reservation.reservation_id = invoice.reservation_id
    LEFT JOIN reservation_status ON reservation_status.reservation_status_id = reservation.reservation_status_id
WHERE
    invoice_status.invoice_status in ('Paid', 'Partially Refunded')
    AND invoice.has_been_dispersed = 0
    AND reservation_status.reservation_status in ('Closed', 'Ended', 'Cancelled')
GROUP BY
    invoice.invoice_id,
    invoice.total,
    invoice.reservation_id,
    invoice_status.invoice_status,
    invoice.charge_customers_payment_processing_fee,
    invoice.payment_processor_fee,
    invoice.vquip_fee,
    invoice.company_id; 



    
    
DROP TEMPORARY TABLE refunds;
DROP TEMPORARY TABLE payments;

I am write a sql statement to select all payments and refunds that are on an invoice. With the below basic statement i am seeing duplicate columns of data when i expect NULL where it should be null.

This query returns the below results:

SELECT
    invoice.invoice_id,
    refund.amount as refund_amount,
    payment.amount as payment_amount
FROM
    invoice
    LEFT JOIN refund_invoice ON refund_invoice.invoice_id = invoice.invoice_id
    LEFT JOIN refund ON refund.refund_id = refund_invoice.refund_id
    LEFT JOIN payment_invoice ON payment_invoice.invoice_id = invoice.invoice_id
    LEFT JOIN payment ON payment.payment_id = payment_invoice.payment_id
where invoice.company_id = 59
and invoice.reservation_id = 1157

Invoice ID	Refund Amount	Payment Amount
1277	222.5	222.5
1277	277.5	222.5
1277	222.5	343.76
1277	277.5	343.76

I would be expecting where i can select the rows by refund and payment seperately rather joined. Something like below. This way i can SUM the totals with out duplicates.

Invoice ID	Refund Amount	Payment Amount
1277	NULL	222.5
1277	222.5	NULL
1277	NULL	343.76
1277	277.5	NULL

Here is the final query that i would like to use. This works when there aren't the same amount of payments/refunds records for for the invoice.

SELECT
    invoice.invoice_id,
    invoice.company_id,
    invoice_status.invoice_status,
    invoice.total AS invoice_total_amount,
    IFNULL(
        reservation.reservation_id, 'ADHOC INVOICE'
    ) AS reservation_id,
    IFNULL(
        SUM(refund.amount),
        0
    ) AS refund_amount,
    IFNULL(
        SUM(payment.amount),
        0
    ) AS payment_amount,
    invoice.payment_processor_fee,
    invoice.vquip_fee,
    IFNULL(
        SUM(payment.amount),
        0
    ) - IFNULL(
        SUM(refund.amount),
        0
    ) AS gross_amount_to_be_dispersed,
    IFNULL(
        SUM(payment.amount),
        0
    ) - IFNULL(
        SUM(refund.amount),
        0
    ) 
     - IFNULL(
        SUM(invoice.vquip_fee),
        0
    ) 
    - IFNULL(
        CASE WHEN invoice.charge_customers_payment_processing_fee = 0 THEN IFNULL(
            invoice.payment_processor_fee, 0
        ) WHEN invoice.charge_customers_payment_processing_fee = 1 THEN 0 ELSE 0 END,
        0
    ) AS amount_to_be_dispersed,
    invoice.charge_customers_payment_processing_fee
FROM
    invoice
    INNER JOIN invoice_status ON invoice_status.invoice_status_id = invoice.status_id
    LEFT JOIN reservation ON reservation.reservation_id = invoice.reservation_id
    LEFT JOIN reservation_status ON reservation_status.reservation_status_id = reservation.reservation_status_id
    LEFT JOIN refund_invoice ON refund_invoice.invoice_id = invoice.invoice_id
    LEFT JOIN refund ON refund.refund_id = refund_invoice.refund_id
    LEFT JOIN payment_invoice ON payment_invoice.invoice_id = invoice.invoice_id
    LEFT JOIN payment ON payment.payment_id = payment_invoice.payment_id
WHERE
    invoice_status.invoice_status in ('Paid', 'Partially Refunded')
    AND (
        payment.processor_payment_id IS NOT NULL || refund.processor_refund_id IS NOT NULL
    )
    AND invoice.has_been_dispersed = 0
    AND reservation_status.reservation_status in ('Closed', 'Ended', 'Cancelled')
GROUP BY
    invoice.invoice_id,
    invoice.total,
    invoice.reservation_id,
    invoice_status.invoice_status,
    invoice.charge_customers_payment_processing_fee,
    invoice.payment_processor_fee,
    invoice.vquip_fee,
    invoice.company_id

Notes - invoice table can have many refunds and many payments invoice_refund and invoice_payment join the refund and payments records to an invoice.

This is not how sets work, unfortunately. You have to do the summing up in subqueries before joining them to the invoice table. — Shadow, Nov 08 '21 at 18:09
ah i was thinking this but hoping for mySQL magic. Thanks for the response. — jtmg.io, Nov 08 '21 at 18:20
@Shadow I added the solved query. Thanks for the insight on this — jtmg.io, Nov 08 '21 at 19:26

MySQL Left Join Statement Duplicating records

0 Answers0