SQlite - Modify SQL query to remove records found in separate table-CodePudding

I have a SQlite database with two table with identical schemas. They contain student exam data from two different sources.

 ──────── ───────────── ───────────────── ────────────────────────────────────── ───────────── ──────── 
| rowid  | student_id  | level           | name                                 | start       | grade  |
 ──────── ───────────── ───────────────── ────────────────────────────────────── ───────────── ──────── 
| 0      | 10001       | Diploma         | Business                             | 2022-04-01  | Pass   |
| 1      | 10001       | Standard Grade  | Mathematics                          | 2013-08-06  | Pass   |
| 2      | 10002       | Intermediate 1  | Hospitality: Practical Cookery       | 2013-08-06  | Pass   |
| 3      | 10003       | Unlevelled      | Elementary Food Hygiene Plus         | 2016-04-28  | Pass   |
| 4      | 10004       | SCQF Level 4    | Employability Award                  | 2015-06-18  | Pass   |
| 5      | 10004       | Level 1         | SVQ in Food Preparation and Cooking  | 2015-06-18  | Pass   |
| 6      | 10004       | Standard Grade  | Geography                            | 2013-08-06  | Fail   |
| 7      | 10005       | Standard Grade  | Social & Vocational Skills           | 2013-08-06  | Pass   |
| 8      | 10006       | Standard Grade  | Art and Design                       | 2013-08-06  | Pass   |
| 9      | 10006       | Standard Grade  | English                              | 2013-08-06  | Pass   |
| 10     | 10007       | Intermediate 1  | Chemistry                            | 2013-08-06  | Pass   |
 ──────── ───────────── ───────────────── ────────────────────────────────────── ───────────── ────────

Table A is already filtered using the below SQL query to filter out exams which students failed then resat and passed. It uses EXCEPT to remove the previous failed attempt leaving the new remaining pass.

    query_A = """
        SELECT * FROM Table_A
        EXCEPT
        SELECT t1.*
        FROM Table_A t1
        WHERE t1.grade = 'Fail'
        AND EXISTS (
                SELECT *
                FROM Table_A t2
                WHERE (t2.student_id, t2.level, t2.name) = (t1.student_id, t1.level, t1.name)
                AND t1.grade <> t2.grade
            )
    """

Table B (while the schema is the same) contains different records, some but not all are also present in Table A.

Essentially, I would like to remove any records that are present in Table_B from Table_A by modifying the query_A statement while retaining the filter on Table_A created by the EXCEPT command.

CodePudding user response：

I would like to remove any records that are present in Table_B from Table_A by modifying the query_A statement while retaining the filter on Table_A created by the EXCEPT command

Add one more EXCEPT:

SELECT * FROM Table_A
EXCEPT
SELECT t1.*
FROM Table_A t1
WHERE t1.grade = 'Fail'
AND EXISTS (
  SELECT *
  FROM Table_A t2
  WHERE (t2.student_id, t2.level, t2.name) = (t1.student_id, t1.level, t1.name)
  AND t1.grade <> t2.grade
)
EXCEPT
SELECT * FROM Table_b;