[PHP] Help on this query, it is complex xD

[kito2]

I have this query:

SELECT `p`.`name` AS `name`, `p`.`level` AS `level`, `p`.`vocation` AS `vocation`, `p`.`promotion` AS `promotion`, COUNT(`p`.`name`) as `frags`
 FROM `killers` k
 LEFT JOIN `player_killers` pk ON `k`.`id` = `pk`.`kill_id`
 LEFT JOIN `players` p ON `pk`.`player_id` = `p`.`id`
 GROUP BY `name`
 ORDER BY `frags` DESC, `name` ASC
 LIMIT 0,1;

It shows the top killer/fragger of the server, but I need to adjust it to show the top killer/fragger of a guild (for example guild id equal 8).

 

[Chris]

SELECT `guild`.`name` AS `guildname`, `p`.`name` AS `name`, `p`.`level` AS `level`, `p`.`vocation` AS `vocation`, `p`.`promotion` AS `promotion`, COUNT(`p`.`name`) as `frags`  FROM `killers` k 
 LEFT JOIN `player_killers` pk ON `k`.`id` = `pk`.`kill_id` 
 LEFT JOIN `players` p ON `pk`.`player_id` = `p`.`id` 
 LEFT JOIN `guild_ranks` AS `gr` ON `gr`.`id` = `p`.`rank_id`
 LEFT JOIN `guilds` AS `guild` ON `guild`.`id` = `gr`.`guild_id`
 WHERE `guild`.`id` = 8
 GROUP BY `name` 
 ORDER BY `frags` DESC, `name` ASC 
 LIMIT 0,1;

 

[kito2]

Thanks, working
It was easy, I didn't tried that haha!