1

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  |  
+----+------+-------+-------+-------+-------+
sisanared
  • 4,175
  • 2
  • 27
  • 42
Thomas Haywood
  • 9
  • 6
  • Why the tags "javascript" and "node.js"? – Lucero Oct 15 '16 at 11:07
  • that's what i'm using it in? – Thomas Haywood Oct 15 '16 at 11:08
  • They have no relevance whatsoever to the question... – Lucero Oct 15 '16 at 11:09
  • sorry about that any ideas though? – Thomas Haywood Oct 15 '16 at 11:10
  • Don't you get an error for having the `MAX` function in there without an `GROUP BY`? Please enter the query with some data into http://sqlfiddle.com/ and post the link... – Lucero Oct 15 '16 at 11:11
  • No, it only shows what comes up above – Thomas Haywood Oct 15 '16 at 11:12
  • ¿Is that the real query or you are making some JOINs? That query result must be empty (id=12 implies it can't return id=null results) – Troglo Oct 15 '16 at 11:13
  • @Troglo MySQL works in mysterious ways... http://sqlfiddle.com/#!9/91f9b/1/0 - see also http://stackoverflow.com/questions/28497082/mysql-aggregate-functions-without-group-by-clause – Lucero Oct 15 '16 at 11:16
  • Sorry, you are right. The MAX has weird effect in your query – Troglo Oct 15 '16 at 11:20
  • 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 GROUP BY id – Troglo Oct 15 '16 at 11:24
  • @Troglo Thanks that works, thanks – Thomas Haywood Oct 15 '16 at 11:28
  • You´re welcome. I assumed you wanted to group by player.id – Troglo Oct 15 '16 at 11:29
  • Note that Troglo's query will take any value for the columns which are not aggregated and not included in the group by. Also, since there is no join condition for the two tables and the result is limited to ID 12 I don't get what the expected outcome would be... so the query now no longer returns rows but I don't think that it is correct. – Lucero Oct 15 '16 at 11:30

1 Answers1

1

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.
Lucero
  • 59,176
  • 9
  • 122
  • 152