Select users who have records equal to y but not x

Question

I have a table which contains historical records for the votes given to entrants in a competition. A simplified example of the structure is:

id | entrantId | roundId | judgeId

Some example data:

401 | 32 | 3 | 4
402 | 32 | 3 | 5
403 | 35 | 3 | 4
404 | 32 | 4 | 4
405 | 36 | 3 | 4
406 | 36 | 3 | 10

I need to get all records of users who have a judgeId = 4 and the roundId = 3 but where the same 'entrantId' doesn't have a record of any kind where roundId = 4

So using the data above, I would be looking to retrieve records 403 and 405 since these entrants(35 & 36) have records for judge 4 in round 3 but have no records in round 4 (eg roundId = 4).

I'm assuming I'd need to have a SELECT statement that uses an 'IN' based clause, but I don't know enough about this to be able to build a suitable query.

Answer 1

You can use correlated subquery:

SELECT *
FROM tab t1
WHERE judgeId = 4 
  AND roundId = 3
  AND NOT EXISTS (SELECT 1
                  FROM tab t2
                  WHERE t1.entrantId = t2.entrantId
                    AND t2.roundId = 4);

LiveDemo

Output:

╔═════╦═══════════╦═════════╦═════════╗
║ id  ║ entrantId ║ roundId ║ judgeId ║
╠═════╬═══════════╬═════════╬═════════╣
║ 403 ║        35 ║       3 ║       4 ║
║ 405 ║        36 ║       3 ║       4 ║
╚═════╩═══════════╩═════════╩═════════╝

---

Alternatively using windowed functions:

WITH cte AS(
SELECT *,
  c = COUNT(CASE WHEN roundId = 4 THEN 1 END) OVER (PARTITION BY entrantId)
FROM #tab
)
SELECT id,entrantId,roundId,judgeId
FROM cte
WHERE c = 0
  AND judgeId = 4 
  AND roundId = 3;

LiveDemo2