susnewyork
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.
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This solution worked for me and produced accurate data based on my tests. Thanks.
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