SQL: choose one categorical value and exclude others

Question

I have a table of patient ID and the medication they use. If patient fails drug A they will use B, while further drug resistance leads to drug C, and so on.

I want to find out patients who use certain drug(i.e. 'B') and stopped there without trying new drug on the list. My code is dumb and not efficient if the Medication list is short.

PatientID   MedicationName
13          A
13          B
13          C
32          A
32          A+
32          B
32          C
38          A
38          C
38          D
42          B
42          F
42          G
53          E
53          F

select *  
from PatientMaster 
where MedicationName = 'B'
  and PatientID not in (
      select PatientID 
      from PatientMaster
      where MedicationName in ( 'C', 'D', 'E', 'F', 'G' ))

If my MedicationName list contains >1000 names, how do I have a kinda "cut-off" for categorical value and select patients who stopped at certain drugs?

how do you know which is the medication order? I mean, is really alphabetical order (like first A, then B, then C,...) or there is some other criteria? Or, do you have an id in that table (primary key) that can tell us which row for "patientID 42" is the last one? — Mark, Jun 13 '18 at 12:48
Which [DBMS product](https://en.wikipedia.org/wiki/DBMS) are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... — , Jun 13 '18 at 12:50
Hi Mark, Thanks for the question. The drug names have no order as A = 'Gleevec', B= 'Stutent', E = 'Rego'. The sequences of drug choice is based on clinical information case by case. PatientID is the primary key and what I showed is the joined table. — Yu Wang, Jun 13 '18 at 15:32

score 0 · Answer 1 · answered Jun 13 '18 at 12:50

I would try something like this, using a subquery that assure there's a single drug (B) for a particular patient:

select *
from PatientMaster p1
join ( select PatientID
       from PatientMaster 
       group by PatientID
       having count(MedicationName) = 1
     ) p2 on p1.PatientID = p2.PatientID 
where MedicationName = 'B'

score 0 · Answer 2 · answered Jun 13 '18 at 13:07

You can have a sequence(if using ORACLE) or identity to log these medication entries. So that, we will know, which medication was tried first.

You can use below query in SQL SERVER.

;WITH CTE_StoppedMedication AS
(
SELECT PatientId,MedicationName AS StoppedAtMedication
FROM patientLog AS Plo
WHERE PatientLogId = (SELECT MAX(PatientLogId)FROM PatientLog AS Pli
                      WHERE Pli.PatientId = Plo.PatientId)
)
SELECT * FROM CTE_StoppedMedication 
WHERE StoppedAtMedication = 'B;

score 0 · Answer 3 · answered Jun 13 '18 at 13:13

Try writing your query as:

select pm.*  
from PatientMaster pm
where pm.MedicationName = 'B' and
      not exists (select 1
                  from PatientMaster pm2
                  where pm2.patientID = p.patientID and
                        pm2.MedicationName in ( 'C', 'D', 'E', 'F', 'G' 
                 );

Then, create indexes on PatientMaster(patientId, MedicationName) and PatientMaster(MedicationName).

SQL: choose one categorical value and exclude others

3 Answers3