Joining on a group by

Question

Lets say that I have three tables, customers, orders and orderDetails.

I'm doing this:

SELECT orders.ordersId, sum(orderDetails.total)
FROM orders
LEFT OUTER JOIN orderDetails ON orders.ordersId = orderDetails.ordersId 
GROUP BY orders.ordersId 

But lets say the orders table contains customersId. How do I join on the customers table so that I can also add the customer's name to the fields selected?

Thanks,

Barry

Answer 1

SELECT orders.ordersId, sum(orderDetails.total), customer.name
FROM orders
LEFT OUTER JOIN orderDetails ON orders.ordersId = orderDetails.ordersId
LEFT OUTER JOIN customer on customer.customerid = orders.customerid 
GROUP BY orders.ordersId , customer.name

Try that out or something similar.

Answer 2

you can do it this way, which will let you get more than customer name if needed:

SELECT o.ordersId, o.orderTotal, c.customername, c.(other customer data)
FROM 
(
    SELECT orders.ordersId
            , sum(orderDetails.total) as orderTotal
            , orders.customersid
    FROM orders
    LEFT OUTER JOIN orderDetails 
            ON orders.ordersId = orderDetails.ordersId 
    GROUP BY orders.ordersId, orders.customersid
) o
LEFT JOIN customers c
    ON o.customersid = c.customersid