mysql inner join 给出不好的结果(？)

Question

以下 sql 调用工作正常，为客户返回正确的零售总额:

SELECT  customer.id,
        customer.first_name, 
        customer.last_name,
        SUM(sales_line_item_detail.retail) AS total_retail
FROM sales_line_item_detail
    INNER JOIN sales_header 
        ON sales_header.id = sales_line_item_detail.sales_header_id
    INNER JOIN customer 
        ON customer.id = sales_header.customer_id
GROUP BY sales_header.customer_Id
ORDER BY total_Retail DESC
LIMIT 10

但是，我还需要它来返回客户的电话和电子邮件地址。请记住，并非所有客户都有电子邮件地址和电话号码。每当我离开加入电子邮件和数字表时，它都会使 total_retail 金额减少数千，我不确定为什么。

以下查询为 total_retail 字段给出了完全错误的结果:

    SELECT  customer.id,
        customer.first_name, 
        customer.last_name,
        IF(
            ISNULL( gemstore.customer_phone_numbers.Number), 
            'No Number..', 
            gemstore.customer_phone_numbers.Number
            ) AS Number,
        IF(
            ISNULL(gemstore.customer_emails.Email), 
            'No Email...', 
            gemstore.customer_emails.Email
            ) AS Email,
        SUM(sales_line_item_detail.retail) AS total_retail,
FROM sales_line_item_detail
    INNER JOIN sales_header 
        ON sales_header.id = sales_line_item_detail.sales_header_id
    INNER JOIN customer 
        ON customer.id = sales_header.customer_id
    LEFT JOIN gemstore.customer_emails 
        ON gemstore.customer_emails.Customer_ID = gemstore.customer.ID
    LEFT JOIN gemstore.customer_phone_numbers 
        ON gemstore.customer_phone_numbers.Customer_ID = gemstore.customer.ID
GROUP BY sales_header.customer_Id
ORDER BY total_Retail DESC
LIMIT 10

非常感谢任何帮助弄清楚为什么它会丢弃我的结果。

谢谢!

Answer 1

是否有可能在 customer_emails 或 customer_phone_numbers 表中有多个 Customer_ID 记录？