Select Users who have never passed an exam

Question

Suppose that I have a table called exam. Each row contains:

examDate
userId
passed

So, we might have the following results:

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

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

I do not want to do something really inefficient like:

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

Answer 1

You want to find out two facts from the same table: a user failed the test, and the same user never passed the test. So what you want to do is join the table to itself.

Because one of those is a non-existence test, that join should be a null-left-join. You want to say select rows where no row exists that matches the join condition.

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

Whilst it will naturally depend on your schema, DBMS and indexing, joins like this are indeed often more efficient than a subquery.

Answer 2

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 a side note, I noticed that you are using characters to represent a true/false value. You might have no control over this but it will always be advisable to use the Boolean datatype for this column because SQL is much quicker at comparing bool values to text values. The query would be contain: Passed = 0 or 1 respectively.

Answer 3

Hi,

here's 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

Answer 4

In a single pass:

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