php - COALESCE(SUM(), 0) JOINED 表列与 GROUP BY 不显示所有结果

Question

我正在尝试构建一个查询，用于检索日期时间范围内的所有预订并加入付款表以计算总付款以及其他一些内容。该查询似乎工作得很好，只是它遗漏了没有付款的预订。任何帮助将不胜感激。

SELECT
    reservations.reservation_id ,
    properties. NAME ,
    properties.address1 AS prop_address ,
    reservations.last_name ,
    reservations.arrival_date ,
    reservations.departure_date ,
    reservations.date_created ,
    reservations.contract_filename ,
    reservations.contract_signed ,
    reservation_payments.date_of_payment ,
    (
        SUM(
            reservations.cleaning_fee + reservations.processing_fee + reservations.pool_heat_fee + reservations.special_fees + reservations.hoa_fees + reservations.rental_fee + reservations.taxes
        ) - COALESCE(
            SUM(
                reservation_payments.amount_paid
            ) ,
            0
        )
    ) AS sub_total ,
    COALESCE(
        SUM(
            reservation_payments.amount_paid
        ) ,
        0
    ) AS total_paid_to_date ,
    reservations.balance_due_date ,
    (
        SELECT
            GROUP_CONCAT(
                DISTINCT user_meta.first_name
                ORDER BY
                    associated_sales_staff.display_order ASC SEPARATOR ", "
            )
        FROM
            associated_sales_staff
        JOIN user_meta ON user_meta.user_id = associated_sales_staff.user_id
        WHERE
            (
                associated_sales_staff.reservation_id = reservations.reservation_id
            )
    ) AS sales_agents
FROM
    reservations
JOIN properties ON properties.prop_id = reservations.property_id
JOIN reservation_payments ON reservation_payments.reservation_id = reservations.reservation_id
WHERE
    properties.active_for_cleaning_schedule = 1
AND reservations.arrival_date >= "2017-05-15"
AND reservations.departure_date <= "2017-05-30"
GROUP BY
    reservations.reservation_id
HAVING
    (sub_total >= 1)
ORDER BY
    total_paid_to_date ASC

Answer 1

我发现问题出在reservation_ payment表的JOIN上。

我更新自:

加入reservation_ payments ONreservation_ payments.reservation_id=reservations.reservation_id

致:

左连接reservation_ paymentsONreservation.reservation_id =reservation_ payments.reservation_id