MySQL Query Guidance Needed

[Paul Seamons]

>     READS SQL DATA
> BEGIN
> 		DECLARE total DOUBLE DEFAULT 0.0;
>
> 		SELECT SUM( value ) INTO total FROM (SELECT (IFNULL( SUM(
> items.basePrice * lost.quantity * IF( items.techLevel >1, 10, 1 ) ) , 0
> ) + ships.basePrice * IF( ships.techLevel >1, 10, 1 )) / count(DISTINCT
> inv.player_id) AS value FROM kb__killmails AS mails INNER JOIN
> kb__involved AS inv ON (mails.id = inv.mail_id) LEFT JOIN kb__items_lost
> AS lost ON ( lost.mail_id = mails.id ) LEFT JOIN kb__items AS items ON (
> lost.item_id = items.typeID ) INNER JOIN kb__items AS ships ON (
> mails.ship_id = ships.typeID ) WHERE mails.id IN (SELECT inv.mail_id AS
> involved FROM kb__involved AS inv INNER JOIN kb__killmails AS kills ON
> (inv.mail_id=kills.id) WHERE inv.player_id=check_id GROUP BY
> inv.mail_id) GROUP BY mails.id) AS a;
>
> 		RETURN total;
> 	END

If I was the DBA I wouldn't let that near the system.  It is great that it 
works but that is a maintenance nightmare.  Add some whitespace and 
indentation.  Make it readable.

I'm not talking about how complex it is - the complexity is fine.  The 
readability leaves much to be desired.  How many sub selects are there? Can 
you tell just by glancing at it?

Indent every sub select.  Gutter your SQL key words.  Line up your conditions.  
You'll be happy you did later.

Paul