In MySQL, I have two tables. First is members. Second is member_experience. The member_experience table contains values of all the XP that members earn and what they get it for. I want to write a query that selects members and orders results by the total of their xp. The query that I have written doesn't return any results.
CREATE TABLE IF NOT EXISTS `members` ( `member_id` int(15) NOT NULL AUTO_INCREMENT, `group_id` int(15) NOT NULL, `display_name` text NOT NULL, `email_address` text NOT NULL, `password` text NOT NULL, `status` tinyint(1) NOT NULL, `activation_code` varchar(16) NOT NULL, `date_joined` text NOT NULL PRIMARY KEY (`member_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS `member_experience` ( `experience_id` int(15) NOT NULL AUTO_INCREMENT, `member_id` int(15) NOT NULL, `value` mediumint(6) NOT NULL, `description` text NOT NULL, `date_earned` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`experience_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
This is my current query:
SELECT m.member_id, xp.member_id, SUM(xp.value) AS total_xp FROM members AS m LEFT JOIN member_experience AS xp ON (xp.member_id = m.member_id) ORDER BY total_xp";