The following SELECT with INNER JOINS works, but does not count it right.
I have a tests table:
TestNumber (int primary key) TestComplete (int 0 = not complete, 1 = complete) ...other stuff
(7, 0) (8, 0) (9, 0)
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)
(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.