Hi,
I've been working out this query now for a while and I thought I had it where I wanted it, but apparently not.
There are two records in the database (orders). The query should return two different rows, but instead returns two rows that have exactly the same values. I think it may be something to do with the GROUP BY or derived tables I'm using but my eyes are tired and not seeing the problem. Can any of you help? Thanks in advance.
SELECT orders.billerID,
orders.invoiceDate,
orders.txnID,
orders.bName,
orders.bStreet1,
orders.bStreet2,
orders.bCity,
orders.bState,
orders.bZip,
orders.bCountry,
orders.sName,
orders.sStreet1,
orders.sStreet2,
orders.sCity,
orders.sState,
orders.sZip,
orders.sCountry,
orders.paymentType,
orders.invoiceNotes,
orders.pFee,
orders.shipping,
orders.tax,
orders.reasonCode,
orders.txnType,
orders.customerID,
customers.firstName AS firstName,
customers.lastName AS lastName,
customers.businessName AS businessName,
orderStatus.statusName AS orderStatus,
IFNULL(orderItems.itemTotal, 0.00) + orders.shipping + orders.tax AS orderTotal,
IFNULL(orderItems.itemTotal, 0.00) + orders.shipping + orders.tax - IFNULL(payments.totalPayments, 0.00) AS orderBalance
FROM orders
LEFT JOIN customers ON orders.customerID = customers.id
LEFT JOIN orderStatus ON orders.orderStatus = orderStatus.id
LEFT JOIN
(
SELECT orderItems.orderID, SUM(orderItems.itemPrice * orderItems.itemQuantity) as itemTotal
FROM orderItems
GROUP BY orderItems.orderID
) orderItems ON orderItems.orderID = orders.id
LEFT JOIN
(
SELECT payments.orderID, SUM(payments.amount) as totalPayments
FROM payments
GROUP BY payments.orderID
) payments ON payments.orderID = orders.id
