Alvaro Carvajal Nakosmai Alvaro Carvajal Nakosmai - 6 months ago 13
SQL Question

MySQL MAX and MIN

I am trying to execute the following query

SELECT `id`,
`name`,
`ownerid`,
`creationdata`,
`motd`,
(SELECT Count(*)
FROM guild_membership a,
players_online b
WHERE a.player_id = b.player_id
AND a.guild_id = id) AS `online`,
(SELECT Max(b.level)
FROM guild_membership a,
players b
WHERE a.player_id = b.id
AND a.guild_id = id) AS `toplevel`,
(SELECT Min(a.level)
FROM players a,
guild_membership b
WHERE a.id = b.player_id
AND b.guild_id = id) AS `lowlevel`
FROM `guilds`
WHERE `name` = 'Wideswing Poleaxe'
LIMIT 1;


The tables used in here are the followin

CREATE TABLE IF NOT EXISTS `players` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`group_id` int(11) NOT NULL DEFAULT '1',
`account_id` int(11) NOT NULL DEFAULT '0',
`level` int(11) NOT NULL DEFAULT '1',
...
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE,
KEY `vocation` (`vocation`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `guilds` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`ownerid` int(11) NOT NULL,
`creationdata` int(11) NOT NULL,
`motd` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY (`name`),
UNIQUE KEY (`ownerid`),
FOREIGN KEY (`ownerid`) REFERENCES `players`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `guild_membership` (
`player_id` int(11) NOT NULL,
`guild_id` int(11) NOT NULL,
`rank_id` int(11) NOT NULL,
`nick` varchar(15) NOT NULL DEFAULT '',
PRIMARY KEY (`player_id`),
FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (`guild_id`) REFERENCES `guilds` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (`rank_id`) REFERENCES `guild_ranks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;


I am trying to get the MAX level and MIN level on the players table inside one guild

However I am always getting toplevel and lowlevel the same value and tis always the lowest level

I am not sure what is wrong

Answer

First thing I notice is: you are using LIMIT without ORDER BY. So from the guilds table you expect to find more than one entry for name = 'Wideswing Poleaxe', but only look at the first the DBMS happens to find. Is this desired?

Next thing I see is the out-dated join syntax. Where did you get this from? A twenty year old book? No, stop, twenty years ago this syntax was already made redundant, so it must be even older ;-) Use explicit joins instead (JOIN ... ON ...)

As to your subqueries: You are comparing with id without any qualifier, so the DBMS will take this to be guild_membership.id or players_online resp. players.id, where you really want it to be guild.id. This should explain that you get unexpected values.

As to how the query is built: You could join to the aggregated player data instead. And use alias names that match the tables.

select 
  guilds.id, 
  guilds.name, 
  guilds.ownerid, 
  guilds.creationdata, 
  guilds.motd, 
  players.online, 
  players.toplevel, 
  players.lowlevel 
from guilds
left join
(
  select 
    gms.guild_id, 
    max(p.level) as toplevel, 
    min(p.level) as lowlevel, 
    (select count(*) from players_online po where po.player_id = p.id) as online
  from guild_membership gms
  join players p on p.id = gms.player_id
  group by gms.guild_id
) players on players.guild_id = guilds.id
where guilds.name = 'Wideswing Poleaxe';

You can change the left outer join (left join) to an inner join (join), if you don't need to see guilds without any player.