mysql inner join giving bad results (?)

The following sql call works fine, returns the correct total retail for customers:

SELECT  customer.id,
        customer.first_name, 
        customer.last_name,
        SUM(sales_line_item_detail.retail) AS total_retail
FROM sales_line_item_detail
    IN开发者_StackOverflow中文版NER 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

However, i need it to return the customers telephone and email addresses as well.. please keep in mind that not all customers have an email address and telephone number. whenever i left join the email and numbers tables, it throws the total_retail amount off by thousands and I am not sure why.

The following query gives completely wrong results for the total_retail field:

    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

Any help figuring out why it is throwing off my results is greatly appreciated.

Thanks!

Is it possible that there are multiple records for a Customer_ID in either the customer_emails or customer_phone_numbers tables?

You'll be matching too many records. Try the query without the group by clause and you'll see which ones and how. Most likely the left join's will duplicate order rows on every customer email/phone match.

I am not totally sure, as i can't test this, but the following might be happening.

If there are more than one email or phone number per customer the final result might get multiplied, because of the new joins.

Imagine the query without the group_by and join to sales:

CustomerId    Email          phoneNumber
1             test@gmx.com   0122233
1             mail@yahoo.com 0122233

The user in this example has 2 mailadresses.

If you would now add the join to sales and the group by, you would have doubled total_retail.

If this should be the case, replacing the LEFT JOIN with an LEFT OUTER JOIN should do the trick. In that case you will however only see the first email/phonenumer of the customer.