MySQL Query Guidance Needed

Matthew Walker rorith at kydance.net
Mon Sep 25 14:04:48 MDT 2006


I'm trying to do a rather tricky query in MySQL, and I'm a bit stumped. I
know I can do what I want if I just offload the work to PHP, but for the
sake of the learning, I'm trying to do it inside SQL.

A bit of background: This is a 'point tracking' site for PVP in EVE
Online. It parses kill reports, and stores them in the database. Now I'm
trying to get it to tally up points per kill, based on the value of the
items destroyed.

Relevant Schema:

CREATE TABLE `items` (
  `id` int(11) NOT NULL auto_increment,
  `typeName` char(100) default NULL,
  `graphicID` int(11) default NULL,
  `basePrice` double default NULL,
  `techLevel` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `typeName` (`typeName`),
  KEY `graphicID` (`graphicID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

CREATE TABLE `lost` (
  `mail_id` bigint(20) unsigned NOT NULL,
  `item_id` bigint(20) unsigned NOT NULL,
  `quantity` bigint(20) unsigned NOT NULL,
  `location` enum('cargo','fit','drone_bay') NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

CREATE TABLE `mails` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `player_id` bigint(20) unsigned NOT NULL,
  `corporation_id` bigint(20) unsigned NOT NULL,
  `alliance_id` bigint(20) unsigned NOT NULL,
  `ship_id` bigint(20) unsigned NOT NULL,
  `system_id` bigint(20) unsigned NOT NULL,
  `timestamp` datetime NOT NULL,
  `hash` varchar(32) NOT NULL,
  `mail` longtext NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

I've got a query that does most of the work of generating the cost of all
of a person's losses:

SELECT IFNULL(SUM(items.basePrice * lost.quantity * IF(items.techLevel >
1, 10, 1)), 0)+(ships.basePrice * IF(ships.techLevel > 1, 10, 1)) AS
totalValue FROM mails LEFT JOIN lost ON (lost.mail_id=mails.id) LEFT JOIN
items ON (lost.item_id=items.id) INNER JOIN items AS ships ON
(mails.ship_id=ships.id) WHERE mails.player_id=6 GROUP BY mails.id

However, this returns 1 row per kill, and I would like to get it to return
simply one value, that is a total of all the losses. Simple to do in PHP
with what I have, but I can't find a way to do it directly in the SQL.

I've tried SUM()'ing a subselect, but that returns null, so I guess I
don't understand subselects. :)

Oh, and flame my language/DB choice all you want. I know them, and they're
good enough for me. If there isn't a way to do this in-db, I'll survive
with doing it in the PHP.

Thanks.
-- 
Matthew Walker
Kydance Hosting & Consulting
LAMP Specialist



More information about the PLUG mailing list