I have managed to hack an SQL Query together and it was working until I realised some members in the database have null names due to the plugin we are using removes non-ascii names. I am displaying these stats on my page and having someone with no name doesn't work too well. I know how to not display NULL when using the WHERE clause but i'm not too sure when no WHERE clause is used.
What I have so far -
SELECT player_stats.player_name, COUNT(player_kill.killer)
FROM player_stats
LEFT JOIN player_kill ON player_stats.player_id = player_kill.killer
GROUP BY player_stats.player_name
HAVING COUNT(player_kill.killer) > 1
ORDER BY COUNT(player_kill.killer) DESC;
The WHERE clause goes between all the JOIN clauses and GROUP BY. If WHERE player_name IS NOT NULL isn't working, the names are probably empty strings, not NULL, so you need to check for that as well.
SELECT s.player_name, COUNT(*) AS count
FROM player_stats AS s
INNER JOIN player_kill AS k ON s.player_id = k.killer
WHERE s.player_name IS NOT NULL AND s.player_name != ''
GROUP BY s.player_name
ORDER BY count DESC;
Also, if you don't want to get 0 counts, use INNER JOIN rather than LEFT JOIN.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments