MySQL - SELECT all patient that are prescribed X but NOT Y

I have a database with a table that lists all medications that are prescribed for each patient.

I'm trying to filter to obtain patients that have been prescribed X but NOT Y.

Patient has been prescribed morphine liquid so they should also have been prescribed and an opioid overdose medication such as naloxone.

So I want to find all patients that have been prescribed Morphine and that are missing Naloxone.

I've tried numerous things, such as exists and sub queries but I'm struggling to get these to work at all.

I'm not overly confident with SQL and I tend to pull the data I need and then filter in a BI program.

The only way I can obtain this information currently is to do two queries, one that looks for all patients on Morphine and then another query that looks for all patients on Naloxone. I then compare the two and identify which patients are missing from the naloxone data.

It's all in 1 table, perhaps I can do a SELF join? A COUNT?

Table name is medorders mo

columns used: mo.ID mo.surname mo.firstname mo.drugname

From the small table above I'd expect to get back only:

• Old Mcdonald, as Joe Bloggs has the naloxone prescribed.
• John Smith has no morphine - ignore
• Kate Ansell has neither - ignore

One approach uses aggregation:

SELECT ID, surname, firstname
FROM medorders
GROUP BY ID, surname, firstname
HAVING SUM(drugname = 'morphine') > 0 AND  -- prescribed morphine
       SUM(drugname = 'naloxone') = 0;     -- not prescribed naloxone

The two criteria in the HAVING clause are summing boolean expressions. Each expression checks for the presence of one of the two drugs, and the sums are being done across all records for each person.