[thelist] mySQL queries

[Beau Hartshorne]

> you cannot get away with that in a real database
>
> if there are multiple invoices per job, which one provides the
individual
> column values (billing_invoice.*)?  no individual values from the
invoice
> table should be available in the job_id groups...

OK, here's what I ended up with after adding client references, and
proper column names:



SELECT
	billing_client.name AS c_name,
	billing_job.name AS j_name,
	billing_job.amount AS j_amount,
	SUM(billing_invoice.amount) AS i_amount_sum
FROM
	billing_client
LEFT JOIN
	billing_job
ON
	(billing_client.client_id = billing_job.client_id)
LEFT JOIN
	billing_invoice
ON
	(billing_job.job_id = billing_invoice.job_id)
GROUP BY
	billing_job.job_id
HAVING
	j_amount > i_amount_sum



There can be more than one invoice per job, and more than one job per
customer.

This is what the mySQL docs say:



MySQL has extended the use of GROUP BY. You can use columns or
calculations in the SELECT expressions that don't appear in the GROUP BY
part. This stands for any possible value for this group. You can use
this to get better performance by avoiding sorting and grouping on
unnecessary items. For example, you don't need to group on customer.name
in the following query:

mysql> SELECT order.custid,customer.name,MAX(payments)
    ->        FROM order,customer
    ->        WHERE order.custid = customer.custid
    ->        GROUP BY order.custid;

In ANSI SQL, you would have to add customer.name to the GROUP BY clause.
In MySQL, the name is redundant if you don't run in ANSI mode.



For the sake of interest, what's the Best Way to write this query?
Should I add more detail to my GROUP BY clause? Or is that unnecessary
because I haven't SELECTed any non-aggregate columns from the invoice
table?

BTW, I'd like to make it clear that my goal here is to be educated, not
to be a pain in the ass.

Thanks,

Beau