Paul Bradley • Solutions Architect & Software Developer


Published:

Using SQLite to find patient IDs present in one table yet missing from another table.

squirrel looking inquisitively at the camera

Table of Contents
  1. The problem statement
  2. The bash script
  3. The create table SQL
  4. The SQL select statement to find records not found in the large cohort table

 The problem statement

At work today, I had to review two lists of patient identifiers. Each list came from a different clinical system and represented the cohort of patients in those respective systems. One list was extensive, with thousands of identifiers. The other list was smaller. So I needed to find which identifiers in the smaller list weren’t present in the more extensive list.

The smaller list still had too many entries to check by hand, and this verification process needed several attempts as administrators tidied up each list. Therefore, it made sense to automate the verification process.

I realised I could automate this using SQLite. I could write a bash script to:

 The bash script

Here is the bash script I came up with:

#!/bin/bash

sqlite3 -cmd ".read create_tables.sql"
        -cmd ".mode csv"
        -cmd ".import cohort1.csv small_cohort"
        -cmd ".import cohort2.csv large_cohort"
        -cmd ".read select_results.sql"
        dupes.db

The -cmd parameter of the sqlite3 command-line utility allows you to specify which commands need executing. We need to use multiple -cmd sequences to execute various commands in order. In the example above, we start by reading and running the create_tables.sql file, which drops and re-creates the tables.

We then switch the mode of operation to CSV before importing the two CSV files. The .import command has two parameters. The first parameter is the name of the CSV file containing your data. The second parameter instructs SQLite on which table the data should be imported into. Finally, we read and execute the select_results.sql file, which will find and list the missing patient identifiers.

 The create table SQL

The SQL code below shows the contents of the create_tables.sql file. It starts by dropping the two tables to clear out the data from the previous verification run and then re-creates the table definitions. Each table has one field to hold the patient identifier. On the large cohort table, we also add a primary index to speed up the searching of entries in that table.

DROP TABLE IF EXISTS small_cohort;
DROP TABLE IF EXISTS large_cohort;

CREATE TABLE small_cohort (
    "nhs" TEXT
);

DROP TABLE IF EXISTS large_cohort;
CREATE TABLE large_cohort (
    "nhs" TEXT  NOT NULL PRIMARY KEY
);

 The SQL select statement to find records not found in the large cohort table

SELECT *
  FROM small_cohort 
 WHERE nhs NOT IN (
     SELECT nhs FROM large_cohort
    );

The above SQL code is the content of the select_results.sql file, which selects all the records from the small_cohort table, which don’t have a corresponding record in the large_cohort table.