This select statement will give you the job name and difference between the actual amounts and quote amounts fo all jobs that have both quotes and actuals:
SELECT job.name,sum(actual.actual_amount)-sum(quote.quote_amount) FROM quote, job, actual WHERE (job.job_id=actual.job_id) and (quote.quote_id=job.job_id) GROUP BY job.name However your requirement that there may be 0 or more actuals and quotes per job means you need to use outer joins. Unfortunately you can't do 2 outer join in a select statement. If you can assume that every job has quotes then you can get your answer like this: SELECT job.name, sum(actual.actual_amount)-sum(quote.quote_amount) FROM quote, {oj job LEFT OUTER JOIN actual ON (job.job_id=actual.job_id)} WHERE ((quote.quote_id=job.job_id)) GROUP BY job.name Hope this helps. Dave Shelley -----Original Message----- From: Beau Hartshorne [mailto:[EMAIL PROTECTED] Sent: Friday, May 30, 2003 4:57 PM To: [EMAIL PROTECTED] Subject: one query, two tables, two aggregate functions Hi, I would like to use one query to compare aggregate values from columns in two separate tables. I am running mysql 3.23.55 (max). Here is a simplified table structure: job - job_id - name quote - quote_id - job_id - quote_amount actual - actual_id - job_id - actual_amount I'm trying to figure out the difference between the actual value and the quoted value. The actual and quote tables may have 0->infinity records. To find the difference for only one job, I could write two queries like this: SELECT sum(quote_amount) AS quote_amount FROM quote GROUP BY job_id WHERE job_id = 1 SELECT sum(actual_amount) AS actual_amount FROM quote GROUP BY job_id WHERE job_id = 1 And then find the difference: quote_amount - actual_amount But I'd like to do this for, say, 100 jobs at a time. Can anyone at least point me in the right direction? Should I be looking at temporary tables? Should I think about my table structure? Thanks!! Beau -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]