Jump to content

Mysql Query Help


matt-uk
 Share

Recommended Posts

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

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

:) 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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...