Include group in data set if condition is met

Posted on 2013-10-23
Last Modified: 2013-10-28

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

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( name, Max( med
from name a
left join medication b
on a.personID=b.PersonID
group by,

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
Question by:susnewyork
LVL 65

Accepted Solution

Jim Horn earned 500 total points
ID: 39595076
<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 (
    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

LVL 69

Expert Comment

by:Scott Pletcher
ID: 39595204
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:

    n.Name, m.Med
FROM dbo.medication m
    n.personID = m.personID
    EXISTS (
        SELECT 1
        FROM dbo.medication m2
            m2.personId = m.personId AND
   = 'benadryl'
    n.Name, m.Med

Author Closing Comment

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

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question