0

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.

1 Answer 1

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
2
  • This works with the minor modification of changing your JOIN line to 'JOIN testusers tu ON (tu.TestNumber = t.TestNumber AND tu.UserId = 25)'. But I do not understand why this works and my method in the original post does not. Would it be possible to explain? Your method even works when you replace 'SELECT tu.*' with 'SELECT COUNT(*)' to get the count. For example, why are you doing the JOIN..ON with a small set of conditions, and then tagging the WHERE on the end? I though when you used JOINs you avoided WHERE conditions?
    – Rewind
    Apr 24, 2016 at 12:33
  • @Rewind I'd recommend you to use the join clause only for establishing relationships between tables. Use the where clause for filtering. In terms of why this works and your strategy doesn't, I can't help you there, I don't understand what you were trying to do! Apr 24, 2016 at 12:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.