Exclude records from list where one record equals a particular value

• NineIron

  SSChampion

  Points: 12536

  More actions

  February 12, 2013 at 6:15 am

  #274048

  Here's an example list. I need to exclude this MRN from my query because it contains the value 250.

  MRN ICD9Code

  123456 245

  123456 432

  123456 250

  123456 123

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 12, 2013 at 6:22 am

  #1586979

  NineIron (2/12/2013)

  ---

  Here's an example list. I need to exclude this MRN from my query because it contains the value 250.

  MRN ICD9Code

  123456 245

  123456 432

  123456 250

  123456 123

  the explanation may be overly simplified, but wouldn't a simple WHERE statement do what you are asking?

  SELECT *

  FROM YOURLIST

  WHERE ICD9Code <> 250

  or do you mean something like this:

  SELECT *

  FROM YOURLIST

  WHERE MRN NOT IN(SELECT MRN FROM YOURLIST

  WHERE ICD9Code = 250)

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• NineIron

  SSChampion

  Points: 12536

  More actions

  February 12, 2013 at 6:33 am

  #1586984

  The second one, yes.

  Thanx.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  February 12, 2013 at 7:33 am

  #1587013

  Lowell (2/12/2013)

  ---

  NineIron (2/12/2013)

  ---

  Here's an example list. I need to exclude this MRN from my query because it contains the value 250.

  MRN ICD9Code

  123456 245

  123456 432

  123456 250

  123456 123

  the explanation may be overly simplified, but wouldn't a simple WHERE statement do what you are asking?

  SELECT *

  FROM YOURLIST

  WHERE ICD9Code <> 250

  or do you mean something like this:

  SELECT *

  FROM YOURLIST

  WHERE MRN NOT IN(SELECT MRN FROM YOURLIST

  WHERE ICD9Code = 250)

  Or perhaps this:

  SELECT

  *

  FROM

  YOURLIST yl1

  WHERE

  NOT EXISTS(SELECT 1 FROM YOURLIST yl2 WHERE yl2.ICD9Code = 250 and yl2.MRN = yl1.MRN)

• kyurthich06

  SSC Journeyman

  Points: 93

  More actions

  February 12, 2013 at 7:39 am

  #1587017

  Lynn Pettis (2/12/2013)

  ---

  Lowell (2/12/2013)

  ---

  NineIron (2/12/2013)

  ---

  Here's an example list. I need to exclude this MRN from my query because it contains the value 250.

  MRN ICD9Code

  123456 245

  123456 432

  123456 250

  123456 123

  the explanation may be overly simplified, but wouldn't a simple WHERE statement do what you are asking?

  SELECT *

  FROM YOURLIST

  WHERE ICD9Code <> 250

  or do you mean something like this:

  SELECT *

  FROM YOURLIST

  WHERE MRN NOT IN(SELECT MRN FROM YOURLIST

  WHERE ICD9Code = 250)

  Or perhaps this:

  SELECT

  *

  FROM

  YOURLIST yl1

  WHERE

  NOT EXISTS(SELECT 1 FROM YOURLIST yl2 WHERE yl2.ICD9Code = 250 and yl2.MRN = yl1.MRN)

  Or even this

  SELECT *

  FROM YOURLIST Y

  LEFT JOIN YOURLIST as YL on YL.ICD9Code = 250

  WHERE YL.ICD9Code IS NULL

• NineIron

  SSChampion

  Points: 12536

  More actions

  February 12, 2013 at 7:53 am

  #1587026

  Thanx for your input.