matt-uk Posted August 26, 2009 Share Posted August 26, 2009 Hi I have two tables one called orders the other called payments.This query is ment to show all outstanding orders that havent been fully paid. You can add values into the payments to add up to the value in the orders table How would I SELECT ALL outstanding ORDERS even if there are no records in the payments table. Currently there has to be a payment record with the value of 0 in.I have tried adding the following code that is underlined but only displays one of the orders that has no payments SELECT orders.*, payments.*, round(sum(amountpaid),2)AS apaidFROM ordersLEFT JOIN paymentsON orders.id=payments.orderidWHERE (writeoff IS NULL OR writeoff = '') AND (nocontinue IS NULL OR nocontinue = '')GROUP BY orderidHAVING sum(amountpaid) < orders.price OR orderid IS NULL I hope this makes sense. If not I'll try and explain further Link to comment Share on other sites More sharing options...
justsomeguy Posted August 26, 2009 Share Posted August 26, 2009 If you remove the HAVING clause does it return all rows? What about this:HAVING sum(amountpaid) != orders.price Link to comment Share on other sites More sharing options...
matt-uk Posted August 26, 2009 Author Share Posted August 26, 2009 oooh, close! I see what you where trying to do. It returns the same as before.If I remove the HAVING clause it returns all rows that have a payment value AND only 1 row that hasn't got payment value. (there are 3)Is there an easier way of showing you?ORDERS table has these rows:idcustomeridordertypeorderdescpriceoldpricedateorderedPAYMENTS table has these rows:idorderidamountpaiddatepaidwriteoffnocontinue Link to comment Share on other sites More sharing options...
justsomeguy Posted August 26, 2009 Share Posted August 26, 2009 Are the other two rows being knocked out by the WHERE clause? Link to comment Share on other sites More sharing options...
matt-uk Posted August 26, 2009 Author Share Posted August 26, 2009 hmmm, just tried taking the WHERE clause out.no those 2 rows still arn't showing Link to comment Share on other sites More sharing options...
justsomeguy Posted August 26, 2009 Share Posted August 26, 2009 It's kind of hard to debug without the data, can you export the tables or something? Link to comment Share on other sites More sharing options...
matt-uk Posted August 26, 2009 Author Share Posted August 26, 2009 ok. I will upload the data as soon as i can.thanks for all your help Link to comment Share on other sites More sharing options...
matt-uk Posted August 27, 2009 Author Share Posted August 27, 2009 woo-hoo! I got it. Had a real hard think about it and it was:SELECT orders.*, payments.*, round(sum(amountpaid),2) AS apaidFROM ordersLEFT JOIN payments ON orders.id=payments.orderidWHERE (writeoff IS NULL OR writeoff = '') AND (nocontinue IS NULL OR nocontinue = '') OR (payments.orderid IS NULL)GROUP BY orderid, orders.idHAVING sum(amountpaid) < orders.price OR (payments.orderid IS NULL)that got those data record escapees!! Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.