mntruell mntruell - 1 month ago 8
SQL Question

SELECT in WHERE clause that uses outer information

I want to select all users that have an average timeout rate of less that 50%:

SELECT *
FROM user u
WHERE (SELECT Avg(gu.didtimeout)
FROM (SELECT didtimeout
FROM gameuser
WHERE userid = u.userid
LIMIT 50) gu) < 0.5;


However, I am getting this error:

ERROR 1054 (42S22): Unknown column 'u.userID' in 'where clause'


How might I make this query in legal MySQL?

EDIT 1:

Here are the relevant parts of the schemas of
GameUser
and
User
.

CREATE TABLE `User` (
`userID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`userID`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=latin1;

CREATE TABLE `GameUser` (
`gameID` mediumint(8) unsigned NOT NULL,
`userID` mediumint(8) unsigned NOT NULL,
`didTimeout` tinyint(1) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


EDIT 2:

Using MySQL 5.7.11.

Answer

To get the user ids, do this:

SELECT gu.userid 
FROM gameuser gu
GROUP BY gu.userid
HAVING AVG(gu.didtimeout) < 0.5;

You can join in additional user information if that is required.

Comments