Subset a dataset - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Subset a dataset

Author

Topic

3rookerbar
Starting Member

8 Posts

Posted - 2007-07-11 : 15:19:57

I am trying to exclude patients from a dataset. There are multiple records per patid in this dataset. I have the following code:

SELECT meds.PATID, meds.MEDICATION, meds.MEDTYPE
FROM meds INNER JOIN patient ON meds.PATID = patient.PATID
WHERE (((meds.MEDTYPE) Not In ("FI (Fusion Inhibitor)","NNUC (","Non-nucleoside","NRTI & NNUC","NRTI (Nucleoside/tide Rev","PI (Protease Inhibitor)")));

I want to exclude all patient records if the patient had any of the above exclusions ever. If they have the exclusion if one record get rid of the rest of the records for that patient. Right now the code only excludes the particular record.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-11 : 15:26:28

[code]SELECT meds.PATID,
meds.MEDICATION,
meds.MEDTYPE
FROM meds
INNER JOIN patient ON patient.PATID = meds.PATID
LEFT JOIN (
SELECT PATID
FROM meds
WHERE MEDTYPE IN ('FI (Fusion Inhibitor)', 'NNUC (','Non-nucleoside', 'NRTI & NNUC', 'NRTI (Nucleoside/tide Rev', 'PI (Protease Inhibitor)')
) AS d ON d.PatID = meds.Patid
WHERE d.PatID IS NULL[/code]

Peter Larsson
Helsingborg, Sweden

3rookerbar
Starting Member

8 Posts

Posted - 2007-07-11 : 15:41:54

I just realized my code is more simple than I orgianally posted.

SELECT meds.PATID, meds.MEDICATION, meds.MEDTYPE
FROM meds
WHERE (((meds.MEDTYPE) Not In ("FI (Fusion Inhibitor)","NNUC (","Non-nucleoside","NRTI & NNUC","NRTI (Nucleoside/tide Rev","PI (Protease Inhibitor)")));

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-11 : 15:47:25

Hey! This is a great way to increase my post count

SELECT     meds.PATID,
           meds.MEDICATION,
           meds.MEDTYPE
FROM       meds
LEFT JOIN  (
               SELECT PATID
               FROM   meds
               WHERE  MEDTYPE IN ('FI (Fusion Inhibitor)', 'NNUC (','Non-nucleoside', 'NRTI & NNUC', 'NRTI (Nucleoside/tide Rev', 'PI (Protease Inhibitor)')
           ) AS d ON d.PatID = meds.Patid
WHERE      d.PatID IS NULL

Peter Larsson
Helsingborg, Sweden

3rookerbar
Starting Member

8 Posts

Posted - 2007-07-11 : 16:05:59

That worked great except MS Access was rewriting your code when I pasted in the SQL view. I had to change back to what you gave me. You gotta love Access. Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-11 : 16:14:38

quote:
Originally posted by 3rookerbar

You gotta love Access.

Ummmm...in a word....no

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

3rookerbar
Starting Member

8 Posts

Posted - 2007-07-11 : 21:29:58

If Peter would have answered any faster my head would have spun off my shoulders.

But thanks for the advice anyways............

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources