How do I create a join which says a field is not equal to X?

Question

I have the following database table with information about people, diseases, and drugs:

PERSON_T              DISEASE_T               DRUG_T
=========             ==========              ========
PERSON_ID             DISEASE_ID              DRUG_ID
GENDER                PERSON_ID               PERSON_ID
NAME                  DISEASE_START_DATE      DRUG_START_DATE
                      DISEASE_END_DATE        DRUG_END_DATE

I want to write a query which finds all people who had disease_id 52 but did not take drug 34. How do I do that? I tried the following in MySql:

SELECT p.person_id, p.gender, p.name, disease_id, drug_id 
   FROM person_t as p 
   INNER JOIN disease_t on disease_t.person_id = p.person_id 
   RIGHT OUTER JOIN drug_t on drug_t.person_id = p.person_id 
   WHERE disease_id= 52 AND drug_id != 34;

This gives me all of the records in which a person did not take drug_id 34 as opposed to the people who did not take drug_id 34. How would I go about writing the query I want?

Answer 1

You can use NOT IN:

SELECT p.person_id, p.gender, p.name, disease_id
FROM person_t as p 
INNER JOIN disease_t d on disease_t.person_id = p.person_id 
WHERE disease_id = 52
AND p.person_id NOT IN (SELECT person_id IN drug_t WHERE drug_id = 34)

Answer 2

Depending on the optimiser NOT EXISTS may be more efficient than NOT IN. Try them both to see which one works best.

SELECT p.person_id, p.gender, p.name, disease_id, drug_id 
   FROM person_t as p 
   INNER JOIN disease_t on disease_t.person_id = p.person_id 
   WHERE disease_id= 52 AND NOT EXISTS (
       SELECT * from drug_T WHERE person_id = person_t.person_id AND drug_id = 34)

Answer 3

For those who don't want to use a subquery:

   SELECT p.person_id, p.gender, p.name, disease_id
     FROM PERSON_T p 
     JOIN DISEASE_T d ON d.person_id = p.person_id 
LEFT JOIN DRUG_T dt ON dt.person_id = p.person_id
                   AND dt.drug_id = 34
    WHERE disease_id = 52
      AND dt.person_id IS NULL