So my problem below still exists, but I think I have a better way (and SQL example) to describe what's going on. Consider the following SQL:
SELECT gc_info.award,amends.amend,payments.paid FROM gc_info LEFT JOIN amends ON gc_info.gc_number=amends.gc_number LEFT JOIN payments ON amends.gc_number = payments.gc_number WHERE gc_info.gc_number = "06-8479" This gives me 3 columns, award, amend, and paid. There is 1 record in the gc_info table for 06-8479, 1 record in amends for 06-8479, and there are 12 records in payments. The result is 12 columns, each with the same award and amend record repeated for each payment record. How can I arrange the SQL so that the award and amend show up in the first record, then are NULL for the remaining. My problem is resulting from doing a SUM(amend) and getting a result that's 12x what it should be - if I could eliminate the repeated values, I would be okay. Thanks again! Jason ----- Original Message ----- From: Jason Soza <[EMAIL PROTECTED]> Date: Tuesday, August 19, 2003 11:18 am Subject: JOINs > I may be trying to do too much in a single query here, but it > would be nice if I could get it working! Apparently, the version > of MySQL used by my work does not support the WITH ROLLUP feature, > otherwise I think that would work. > > What I have are 3 tables: gc_info, amends, payments. > > 'gc_info' contains a number that represents the original award > given to a contractor. If there are any increases, these are kept > in 'amends', and all payments are kept in 'payments'. > > What I need to do is get three numbers: current total award > (award+amendments), current total paid (SUM(payments)), and > current balance ((awards + SUM(amendments)) - SUM(payments)). > > This almost works with the below query, except that the where > there should only be 1 amendment, 11 more show up, or one for each > of the 12 payments made to the contractor: > > SELECT org_name,FORMAT(award+amend,2) AS current_total, > FORMAT((award+SUM(amend))-SUM(paid),2) AS balance, > FORMAT(SUM(paid),2) AS total_paid FROM contact_info LEFT JOIN > gc_info ON contact_info.id=gc_info.contact_id LEFT JOIN amends ON > gc_info.gc_number=amends.gc_number LEFT JOIN payments ON > gc_info.gc_number = payments.gc_number WHERE gc_info.gc_number = > "06-8479" GROUP BY gc_info.gc_number > > For example, on a contract with an original award of 168,000, one > amendment of 168,000, and 12 payments of 14,000, this gives me: > > |org_name|current_total |balance |total_paid > | > |Org Name|336,000.00 (correct)|2,016,000.00 (incorrect)|168,000.00 > (correct)| > So in FORMAT((award+SUM(amend))-SUM(paid),2), SUM(amend) is adding > up 12 instances of amend instead of 1. > > Any advice would be helpful. I've read through the JOIN section in > the MySQL manual, tried different JOINs, GROUP BYs, etc. but > cannot figure this one out. > > Thank you very much, > Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
