Clever Geek Handbook

Joining a group

Suppose I have three tables, customers, orders and orderDetails.

I'm doing it:

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

But let's say the orders table contains a customerId. How do I join the customer table so that I can add the customer name to the selected fields?

Thank,

Barry

+3


source to share


2 answers


you can do it in a way that allows you to get more than the customer's 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
+2


source 


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 this or something similar.

+3


source






More articles:


All Articles