tenub tenub - 6 months ago 16
MySQL Question

Selecting minimum time for each unique column value where user is not banned

I have a bunch of player records, a bunch of world records, and a bunch of banned players:

describe records


+--------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| type | varchar(3) | NO | MUL | NULL | |
| map | varchar(31) | NO | MUL | NULL | |
| authid | varchar(31) | NO | | NULL | |
| time | decimal(13,6) | NO | | NULL | |
| date | datetime | NO | | NULL | |
+--------+---------------+------+-----+---------+----------------+


describe wrs


+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| map | varchar(31) | NO | MUL | NULL | |
| route | varchar(31) | NO | | NULL | |
| time | decimal(5,2) | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+


describe banned


+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| authid | varchar(31) | NO | UNI | NULL | |
+--------+-------------+------+-----+---------+----------------+


I am trying to select the minimum
time
from the
records
table for each unique
map
where
authid
(a player identifier) is not found in the
banned
table.

I also want to retrieve the world record
time
from
wrs
for the same
map
.

I would like to select
map
,
authid
, and
time
from
records
only if
type
equals "pro" but would like to keep
map
from the list of unique maps and
time
from the corresponding row in
wrs
if the
type
does not equal "pro".

My issue is retrieving the correct/corresponding
authid
from the
records
table when retrieving the minimum
time
/
map
. I've managed to do something like this:

SELECT m.map, minTime, wrTime, wrTime / minTime AS ratio, minTime - wrTime AS diffTime FROM
# get all unique maps from records table
(SELECT map FROM records GROUP BY map) AS m
LEFT JOIN
# find min time for each map where type is pro
(SELECT type, map, MIN(time) AS minTime FROM records WHERE type = "pro" GROUP BY map) AS p
ON m.map = p.map
LEFT JOIN
# find world record time for each map
(SELECT map, MIN(time) AS wrTime FROM wrs GROUP BY map) AS w
ON m.map = w.map


but it does not account for banned players because I cannot retrieve the correct
authid
values when selecting the minimum
time
(aside: I am not even sure the correct
type
would be used in the where clause). I could easily add something like the following to the query to ignore banned players if I had the proper
authid
:

SELECT * FROM records r LEFT JOIN banned b ON r.authid = b.authid WHERE b.authid IS NULL


Hopefully I've made sense. If not, please ask for more information.

Answer

I am not sure that I understood your problem completely, but I think the following SQL might help you with:

My issue is retrieving the correct/corresponding authid from the records table when retrieving the minimum time/map. "

SELECT
    map,
    MIN(time) AS minTime
FROM records
WHERE
    authid NOT IN (SELECT authId FROM banned)
GROUP BY map