MySQL - A Count with INNER JOINS - that multiplies the result by 8

Question

The following SELECT with INNER JOINS works, but does not count it right.

I have a tests table:

Tests

TestNumber (int primary key)
TestComplete (int 0 = not complete, 1 = complete)
...other stuff

with values

(7, 0)
(8, 0)
(9, 0)

TestUsers

TestNumber (int - ties in with TestNumber in Tests)
UserId (int - the person in the test)
UserSlot (int - the seat they have in the test)
ResignedTimestamp (TIMESTAMP - when they resigned from their seat in the test or '1970-01-01 00:00:01' if they have not resigned)

with values

(7, 25, 5, 1970-01-01 00:00:01) this counts
(8, 25, 2, 2016-04-23 12:00:00) this should not count because the one below has filled the slot
(8, 32, 2, 1970-01-01 00:00:01)

What I need to do is count the number of tests a user is signed into and are not complete. It must include all Tests they have not resigned from. (This is easy).

BUT it must also include all the Tests they have resigned from and another User has not taken their place. (This is the tricky bit).

Another user will have been deemed to have taken the place of the User in question if they have an entry in TestUsers with the same TestNumber and the same UserSlot (note both), and they either have not resigned themselves (ResignedTimestamp='1970-01-01 00:00:01') or they have resigned but their ResignedTimestamp is later.

Here is my SQL.

SELECT COUNT(*) FROM testusers tu
    INNER JOIN tests t ON
        t.TestNumber=tu.TestNumber AND
        t.TestComplete=0 AND
        tu.UserId=25
    INNER JOIN testusers tu2 ON
        (tu.ResignedTimestamp='1970-01-01 00:00:01' OR
        (tu.ResignedTimestamp!='1970-01-01 00:00:01' AND
        tu.TestNumber=tu2.TestNumber AND tu.PlayerSlot=tu2.PlayerSlot AND
        (tu2.ResignedTimestamp='1970-01-01 00:00:01' OR tu.ResignedTimestamp < tu2.ResignedTimestamp)
        )
    )

The result counts row (7, 25, 5, 1970-01-01 00:00:01) eight times, and adds to that (8, 25, 2, 2016-04-23 12:00:00) once. So it is giving me an answer of 9 when the answer should just be 1.

Answer 1

Perhaps you want something like this?

SELECT tu.*
FROM tests t
JOIN testusers tu ON (tu.TestNumber = t.TestNumber)
WHERE t.TestComplete = 0 AND  -- below is your tricky bit
      (ResignedTimestamp='1970-01-01 00:00:01' OR
       NOT EXISTS (SELECT 1 FROM testusers WHERE TestNumber = tu.TestNumber AND UserSlot = tu.UserSlot AND UserId <> tu.UserId))
ORDER BY tu.UserId