4

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
3
  • I don't think I'm familiar with an IFNULL keyword. If that's not a typo (ISNULL?) then you should tag this with the correct platform. Commented Feb 23, 2010 at 5:10
  • Try removing the joins one by one until you get the single row you expect. The last one removed is the culprit. Commented Feb 23, 2010 at 5:11
  • IFNULL is the ISNULL equivalent in MySQL Commented Feb 23, 2010 at 5:17

2 Answers 2

5

Typically when you join on many tables and end up with duplicate rows it is because you are not seeing the entire picture. If you were to do a "select *" to see all of the columns included in the query (instead of returning a subset of columns) and compare the resulting rows you would find that somewhere along the way there is a column that contains different data.

Sign up to request clarification or add additional context in comments.

Comments

2

When i have this problem i start commenting out one join at a time (and the associated selected columns) until i find the offending join causing the problem.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.