Choose users who have never passed the exam.

Question

Suppose I have a table called an exam. Each line contains:

examDate
userId
passed

Thus, we could have the following results:

January 1, 2010, 7, false
January 2, 2010, 8, true
January 3, 2010, 7, true
January 3, 2010, 9, false

I want to run a report that includes all users who NEVER passed the exam. In the above example, only userId 9 request should be returned by this request.

I do not want to do something really inefficient, for example:

Select * from exam where passed = 'false' and userId Not In
(Select userId from exam where passed = 'true');

Answer 1

: , . , , .

, - . , , , .

SELECT e0.*
FROM exam AS e0
LEFT JOIN exam AS e1 ON e1.userId=e0.userId AND e1.passed='true'
WHERE e0.passed='false'
AND e1.examDate IS NULL  -- this can be any non-nullable column in e1

, , , , , .

Answer 2

:

select userId 
from exam 
group by userId
having max(passed) = 'false'

Answer 3

Try:

SELECT 
      ExamDate, UserId, Passed 
FROM
      Exam e1
LEFT JOIN
      Exam e2 ON e1.UserId = e2.UserId AND e2.Passed = 'true'
WHERE
     e1.Passed = 'false'
AND 
     e2.UserId IS NULL

On the side of the note, I noticed that you are using characters to represent true / false. You may not have control over this, but it will always be useful to use a Boolean datatype for this column, because SQL compares bool values ​​with text values ​​much faster. The request will contain: Passed = 0 or 1, respectively.

Answer 4

here is my solution, it works 100%!

SELECT 

    ex1.userId,

    COUNT(ex2.userId) AS countPassed

FROM

    exam AS ex1

LEFT OUTER JOIN exam AS ex2

    ON ex2.userId = ex1.userId AND ex2.passed = 'true'

GROUP BY ex1.userId

HAVING countPassed = 0