Sqlite – Return rows where some values are different

April 13, 2022

I have a large table a SQLite database of student exam results. Students often resit failed exams and subsequently pass them as shown with student 10001 below.

+─────────────+────────+──────────────+──────────+────────+─────────────+
| student_id  | level  | name         | outcome  | grade  | date        |
+─────────────+────────+──────────────+──────────+────────+─────────────+
| 10001       | Higher | Mathematics  | Pass     | A      | 01/04/2022  |
| 10001       | Higher | Mathematics  | Fail     | F      | 01/02/2022  |
| 10002       | Higher | English      | Pass     | B      | 01/04/2022  |
+─────────────+────────+──────────────+──────────+────────+─────────────+

I would like to be able to do two things with this data.

Return only the failed exam should it have a also have a pass entry recorded. In other word the row 2nd from the top in the image table.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.
Visit Medevel
Return the whole table but this time without the initial failed exam. In other words everything minus the results from 1.

The exam is considered a resit if the student_id, level and name all match. The other values in the rows can be different.

Any help appreciated.

>Solution :

For the 1st resultset that you want, you can use EXISTS in the WHERE clause:

SELECT t1.*
FROM tablename t1
WHERE t1.outcome = 'Fail'
  AND EXISTS (
        SELECT 1
        FROM tablename t2
        WHERE (t2.student_id, t2.level, t2.name) = (t1.student_id, t1.level, t1.name)
          AND t2.date > t1.date 
          AND t2.outcome = 'Pass'
      );

and for the 2nd resultset, since the requirement is:

everything minus the results from 1

the simplest way to get it is with the 1st query and EXCEPT:

SELECT * 
FROM tablename
EXCEPT
SELECT t1.*
FROM tablename t1
WHERE t1.outcome = 'Fail'
  AND EXISTS (
        SELECT 1
        FROM tablename t2
        WHERE (t2.student_id, t2.level, t2.name) = (t1.student_id, t1.level, t1.name)
          AND t2.date > t1.date 
          AND t2.outcome = 'Pass'
      );

See the demo.

Note that, if the format of the dates in the table is the same as the sample data in your question then these dates are not comparable.
You should change the format to YYYY-MM-DD.