MySQL Query Guidance Needed

Matthew Walker rorith at kydance.net
Wed Sep 27 12:55:18 MDT 2006


Using the advice I got in this thread, I came up with these two stored
functions that do exactly what I want:

CREATE FUNCTION `kill_points`(check_id BIGINT(20) UNSIGNED) RETURNS double
    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

CREATE FUNCTION `loss_points`(check_id BIGINT(20) UNSIGNED) RETURNS double
    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 ) AS value FROM
kb__killmails AS mails 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.player_id=check_id GROUP BY mails.id) AS a;

		RETURN total;
	END

Thanks again for the help, everyone. You've helped me reach a better
understanding of one of the newer features of MySQL.

-- 
Matthew Walker
Kydance Hosting & Consulting
LAMP Specialist



More information about the PLUG mailing list