Multiple LIKE clauses SQL

Question

I am working on a query to show patients who are on certain types of medication. I have a list of a few different meds that I'm seeking -- I am looking to see if there are any patients in the database who have a prescription for any of these medications that is end-dated on or after today's date.

The medications appear by name in a table, so I am trying to find them using LIKE. But once I add more than one med, my query blows up to pull a lot of duplicates (goes from 362 rows to over 32K rows returned), and ignores all the items in my WHERE clause after the meds.

So here's what I've tried:

    select p.PatientID, 
    p.FirstName+' '+p.LastName as 'PatientName', 
    m.endDate, 
    m.Prescriber, 
    m.drugDescription as 'DrugName'
    from Medications m
    join Patients p on p.RCDID = m.PTID
    where m.historyRecord = 'N'
    and m.[current] = 'Y'
    and m.drugDescription like 'Med1%'
    or m.drugDescription like 'Med2%'
    or m.drugDescription like 'Med3%'
    and m.endDate >= GETDATE()

This gives me over 30K duplicates -- and the repeating patient does not meet the following criteria in the WHERE clause:

    where m.historyRecord = 'N'
    and m.[current] = 'Y'        
    and m.endDate >= GETDATE()

(and this patient actually only appears in the database for 21 rows -- Med2 prescribed on 21 different times -- so it's completely gone off the rails)

I tried a subquery also:

    select p.PatientID, 
    p.FirstName+' '+p.LastName as 'PatientName', 
    m.endDate, 
    m.Prescriber, 
    m.drugDescription as 'DrugName'
    from Medications m
    join Patients p on p.RCDID = m.PTID
    where m.historyRecord = 'N'
    and m.[current] = 'Y'
    and exists (select 1 from Medications m1
                where m1.PTID = m.PTID
                and m1.drugDescription like 'Med1%'
                or m1.drugDescription like 'Med2%'
                or m1.drugDescription like 'Med3%'
                and m1.endDate >= GETDATE())

This is what I'm looking for:

PatientID | PatientName | endDate   | Prescriber | DrugName
1         | John Smith  | 2017-10-22| Dr. Jones  | Med1
1         | John Smith  | 2017-10-22| Dr. Jones  | Med3
2         | Mary Doe    | 2017-11-01| Dr. Johnson| Med2
3         | Steve Doe   | 2017-11-15| Dr. Smith  | Med1

Any suggestions? I've done some digging and haven't quite hit on anything that has worked yet, so ideas are appreciated!

Gordon Linoff · Accepted Answer · 2017-10-09 22:14:36Z

5

You need parentheses!

where m.historyRecord = 'N' and
      m.[current] = 'Y' and
      m.endDate >= GETDATE() and
      (m.drugDescription like 'Med1%' or
       m.drugDescription like 'Med2%' or
       m.drugDescription like 'Med3%'
      )

If you are learning SQL, always use parentheses when mixing and and or.

edited Oct 9, 2017 at 22:14

answered Oct 9, 2017 at 22:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Simon Over a year ago

Jesus christ, you caught that faster than speed of light

jfe042 Over a year ago

This returns 0 rows -- I know there are people who meet the m.drugDescription like 'Med1%' (approx 300) and are about 400 more who meet the or 'Med2%' -- it's when Med3 gets into it that everything goes nuts.

BJones Over a year ago

@jfe042, Gordon is right. Perhaps there's something else wrong with your query such as the other filters. I would go through line by line in the WHERE clause and comment out one at a time to see which one is causing you grief.

Gordon Linoff Over a year ago

@jfe042 . . . Perhaps it was with the end date. If you are using or, adding another condition should not cause the query to be empty.

Gordon Linoff Over a year ago

@AlanBurstein . . . You could, if you really believed that the descriptions have those names. I'm guessing the meds have some other name.

|

Collectives™ on Stack Overflow

Multiple LIKE clauses SQL

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related