Tom Haywood Tom Haywood - 1 month ago 8
SQL Question

SQL query showing nulls when i need a empty set

I am using this query but coming up with nulls in all of the columns. how could i get rid of this? So it becomes a Empty set?

SELECT players.id, players.room, players.money, players.cellx, players.celly, max(levels.level) as level
FROM players, levels
WHERE players.xp - levels.xp >= 0 AND id=12;

+----+------+-------+-------+-------+-------+
| id | room | money | cellx | celly | level |
+----+------+-------+-------+-------+-------+
|NULL| NULL | NULL | NULL | NULL | NULL |
+----+------+-------+-------+-------+-------+

Answer

By using the MAX aggregate function, MySQL does an implicit GROUP BY. http://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Suggestions:

  • Use MySQL 5.7.5 or newer, or set the ONLY_FULL_GROUP_BY setting to disable this behavior. You'll get an error message then instead of nonsensical results.
  • Don't join with multiple tables in the FROM clause, use ANSI JOIN syntax instead.
  • Specify what you're looking for in the question; the query as-is doesn't make any sense to me.
  • You might need an explicit GROUP BY or window functions MAX(...) OVER (...) to compute aggregates.
Comments