Find records that match two criteria

• NineIron

  SSChampion

  Points: 12537

  More actions

  June 24, 2013 at 9:59 am

  #295611

  In the table Diagnosis I am trying to pull only those records that have left(Code,3)='491' AND Code of '305.1'

  Any thoughts?

  create table Diagnosis

  (

  IDXMRN varchar(10),

  Problem varchar(100),

  Code varchar(6)

  )

  insert into Diagnosis(IDXMRN, Problem, Code) values('00001768','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00003060','Chronic bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00003172','Chronic bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00003239','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00003239','COPD exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00003239','COPD exacerbation','493.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00004700','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00006190','Chronic bronchitis with emphysema','491.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00006190','Chronic bronchitis with emphysema','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00006578','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00006578','COPD exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00008365','Obstructive chronic bronchitis','491.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00008671','Chronic bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00008671','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00008821','COPD with acute bronchitis','491.22')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00008821','COPD with acute bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00009274','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00009274','COPD exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00009274','COPD exacerbation','493.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00010684','Chronic Bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00010777','COPD (unspecified)','491.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00013245','Chronic bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00014171','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00014171','COPD exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00014171','COPD exacerbation','493.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00015281','Obstructive Chronic Bronchitis With Acute Exacerbation','491.22')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00015281','Obstructive Chronic Bronchitis With Acute Exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00016275','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00016275','COPD exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00019049','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00019049','COPD with acute bronchitis','491.22')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00019049','COPD with acute bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00019492','Obstructive Chronic Bronchitis With Acute Exacerbation','491.22')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00023687','Diffuse Obstructive Chronic Bronchitis','491.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00027188','COPD with acute bronchitis','491.22')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00027188','COPD with acute bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00029298','Chronic Bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00031647','Chronic Bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00031647','Chronic Bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00031647','Chronic Bronchitis','493.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00033274','Obstructive Chronic Bronchitis With Acute Exacerbation','491.22')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00033274','Obstructive Chronic Bronchitis With Acute Exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00038404','COPD with acute bronchitis','491.22')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00040058','Obstructive chronic bronchitis','491.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00040058','Obstructive chronic bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00040737','COPD (unspecified)','491.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00041056','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00043090','COPD with acute bronchitis','491.22')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00043090','COPD with acute bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00043090','COPD with acute bronchitis','493.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00045601','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00047657','Chronic Bronchitis With Acute Exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00048938','Obstructive chronic bronchitis','491.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00048938','Obstructive chronic bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00048938','Obstructive chronic bronchitis','493.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00049077','Chronic bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00050611','Chronic bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00050611','Chronic bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00051378','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00051378','COPD exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00052112','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00052112','COPD exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00052676','Chronic bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00052676','Chronic bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00053093','Chronic Bronchitis With Acute Exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00053661','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00053661','COPD with acute bronchitis','491.22')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00053661','COPD exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00055080','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00056369','COPD (unspecified)','491.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00056595','COPD with acute bronchitis','491.22')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00056595','COPD with acute bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00056595','COPD with acute bronchitis','493.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00059502','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00061302','Chronic bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00063289','COPD (unspecified)','491.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00063289','Obstructive Chronic Bronchitis With Acute Exacerbation','491.22')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00063289','COPD (unspecified)','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00063289','Obstructive Chronic Bronchitis With Acute Exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00063289','COPD (unspecified)','493.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00063453','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00063453','COPD exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00064343','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00064343','COPD exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00064343','COPD exacerbation','493.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00068729','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00069423','Chronic bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00071895','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00071895','COPD exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00071895','COPD exacerbation','493.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00072552','Chronic Bronchitis With Acute Exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('077171','Chronic bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('077171','Chronic bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00079358','Chronic Bronchitis With Acute Exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00079705','Chronic Bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00079705','Chronic Bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00079752','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00081150','COPD with acute bronchitis','491.22')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00081150','COPD with acute bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00081150','COPD with acute bronchitis','493.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00085093','Chronic Bronchitis With Acute Exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00085093','Diffuse Obstructive Chronic Bronchitis','491.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00085093','Chronic Bronchitis With Acute Exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00085093','Diffuse Obstructive Chronic Bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00086068','Obstructive Chronic Bronchitis With Acute Exacerbation','491.22')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00090144','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00092788','COPD with acute bronchitis','491.22')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00092788','COPD with acute bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00092798','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00092798','COPD exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00095573','Chronic bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00095573','Chronic bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00101205','Obstructive Chronic Bronchitis With Acute Exacerbation','491.22')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00101205','Obstructive Chronic Bronchitis With Acute Exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00103728','Chronic Bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00103744','Chronic bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00104403','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00104403','COPD exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00104403','COPD exacerbation','493.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00105083','Obstructive chronic bronchitis','491.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00105083','Obstructive chronic bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00105868','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00107160','Chronic Bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00110001','Chronic bronchitis with emphysema','491.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00110001','Chronic bronchitis with emphysema','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00110001','Chronic bronchitis with emphysema','493.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00111896','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00113969','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00113969','COPD exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00114392','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00114392','COPD exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00114776','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00115426','Chronic Bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00115426','Chronic Bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00116270','Chronic bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00117604','COPD with acute bronchitis','491.22')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00118102','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00118102','COPD exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00118335','COPD with acute bronchitis','491.22')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00118335','COPD with acute bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00119326','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00119363','COPD with acute bronchitis','491.22')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00119363','COPD with acute bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00119701','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00119701','COPD with acute bronchitis','491.22')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00121040','Chronic bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00121040','Chronic bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00122601','COPD with acute bronchitis','491.22')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00500487','Chronic bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00504921','COPD with acute bronchitis','491.22')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00504921','COPD with acute bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00504921','COPD with acute bronchitis','493.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00529796','Chronic bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00529796','Chronic bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00562184','Obstructive chronic bronchitis','491.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00562184','Obstructive chronic bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00593674','Chronic bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00593674','Chronic bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('00593674','Chronic bronchitis','493.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('724341','Chronic bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('724341','COPD exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('724341','Chronic bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('724341','COPD exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('736385','Chronic bronchitis','491.9')

  insert into Diagnosis(IDXMRN, Problem, Code) values('788882','Chronic Bronchitis With Acute Exacerbation','491.21')

  insert into Diagnosis(IDXMRN, Problem, Code) values('788882','Diffuse Obstructive Chronic Bronchitis','491.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('788882','Chronic Bronchitis With Acute Exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('788882','Diffuse Obstructive Chronic Bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('788882','Chronic Bronchitis With Acute Exacerbation','493.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('788882','Diffuse Obstructive Chronic Bronchitis','493.2')

  insert into Diagnosis(IDXMRN, Problem, Code) values('724399','Chronic bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('736399','Chronic bronchitis','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('788899','Chronic Bronchitis With Acute Exacerbation','305.1')

  insert into Diagnosis(IDXMRN, Problem, Code) values('788999','Diffuse Obstructive Chronic Bronchitis','305.1')

• Matthew Darwin

  SSCertifiable

  Points: 5513

  More actions

  June 24, 2013 at 10:02 am

  #1626834

  Try this:

  SELECT IDXMRN, Problem, Code FROM Diagnosis

  WHERE Code = '305.1'

  OR LEFT(Code, 3) = '491'

  Or have I completely missed the point, and you want the IDXMRN where there is a row for both Code = '305.1' and the 491s?

  Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

• NineIron

  SSChampion

  Points: 12537

  More actions

  June 24, 2013 at 10:06 am

  #1626839

  I'm looking for records that have both 305.1 and 491. These would be people that are smokers and have COPD.

• logitestus

  SSCrazy

  Points: 2928

  More actions

  June 24, 2013 at 10:07 am

  #1626840

  Or another approach (but extremely similar):

  select IDXMRN,Problem,Code

  from dbo.Diagnosis

  where LEFT(CODE,3) = '491' OR LEFT(CODE,5) = '305.1'

• Matthew Darwin

  SSCertifiable

  Points: 5513

  More actions

  June 24, 2013 at 10:09 am

  #1626841

  OK try this:-

  WITH cte AS (

  SELECT d1.IDXMRN

  FROM Diagnosis d1

  INNER JOIN Diagnosis d2

  ON d1.IDXMRN = d2.IDXMRN

  WHERE d1.Code = '305.1'

  AND LEFT(d2.CODE, 3) = '491'

  GROUP BY d1.IDXMRN)

  SELECT d.*

  FROM Diagnosis d

  INNER JOIN cte

  ON d.IDXMRN = cte.IDXMRN

  Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

• NineIron

  SSChampion

  Points: 12537

  More actions

  June 24, 2013 at 10:11 am

  #1626842

  See above comment. If you get some records that only have one of the codes, this shows that the query isn't what I want.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 24, 2013 at 10:11 am

  #1626843

  Matthew Darwin (6/24/2013)

  ---

  Try this:

  SELECT IDXMRN, Problem, Code FROM Diagnosis

  WHERE Code = '305.1'

  OR LEFT(Code, 3) = '491'

  Or have I completely missed the point, and you want the IDXMRN where there is a row for both Code = '305.1' and the 491s?

  I think in that case, you have to join the table agaisnt itself:

  this seems to work for me, i think:

  SELECT T1.*,T2.Problem,T2.Code

  FROM Diagnosis T1

  INNER JOIN Diagnosis T2

  ON T1.IDXMRN = T2.IDXMRN

  WHERE T1.Code = '305.1'

  AND LEFT(T2.Code, 3) = '491'

  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: 12537

  More actions

  June 24, 2013 at 10:15 am

  #1626845

  Thanx. This is what I'm looking for.