Include group in data set if condition is met

Hi,

I need to show a group of data only if one of the records contains specific text.

Ex:
In the raw data below there are two patients who have a medication list. If the data set for the patient contains the med Benedryll, then I need the set to show in the output.

select max(a.name) name, Max(b.med) med
from name a
left join medication b
on a.personID=b.PersonID
group by a.name, b.med

Open in new window


The script above acts like the script I've tried (excluding the where clause) and produces the data below.

Name             Med
Fryy, Peter      Advil
Fryy, Peter      Tylenol
Fryy, Peter      Alieve
Penn, Anna      Advil
Penn, Anna      Benedryll
Penn, Anna      Tylenol
Penn, Anna      Alieve

How would I modify the script so that I only show patients who use benedryll as one of their medications?

The desired result from the script would be to show:

Name             Med
Penn, Anna      Advil
Penn, Anna      Benedryll
Penn, Anna      Tylenol
Penn, Anna      Alieve
LVL 1
susnewyorkAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<air code.  I do my own stunts too>
SELECT n.Name, m.Med
FROM name n
    JOIN medication m on n.personID=m.PersonID
WHERE n.Name IN (
    SELECT DISTINCT n.name
    FROM name n
       JOIN medication m on n.personID=m.PersonID
   WHERE m.Med='Benedryll')
ORDER BY n.Name, m.Med

Open in new window

0
 
Scott PletcherSenior DBACommented:
Can't use name to do the match, because it might be duplicated by different personIDs.  Either way, there's no reason to do two name joins/lookups.  Also, I prefer to use EXISTS rather than IN to possibly get a better data plan:


SELECT
    n.Name, m.Med
FROM dbo.medication m
INNER JOIN dbo.name n ON
    n.personID = m.personID
WHERE
    EXISTS (
        SELECT 1
        FROM dbo.medication m2
        WHERE
            m2.personId = m.personId AND
            m2.med = 'benadryl'
    )
ORDER BY
    n.Name, m.Med
0
 
susnewyorkAuthor Commented:
This solution worked for me and produced accurate data based on my tests. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.