Link to home
Start Free TrialLog in
Avatar of susnewyork
susnewyorkFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of susnewyork

ASKER

This solution worked for me and produced accurate data based on my tests. Thanks.