InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 14:53:41 GMT20http://www.sqlservercentral.com/Forums/Topic1360536-1292-1.aspxYou could also solve this using EXISTS.[code="sql"]select p.*from partients pwhere exists(select * from dx where dx.patiendid=p.partientid and dx.dxcode in (23,24))[/code]I'm not sure if this approach will perform better than Evil Craig F's solution so you need to test it. At least the there is no aggregation steps in my approach.Fri, 21 Sep 2012 10:04:09 GMTNils Gustav Stråbøhttp://www.sqlservercentral.com/Forums/Topic1360536-1292-1.aspxthank you for your responses and link. I'll remember that for future questions I might have. They pseudo code worked fine..thanks for the advice.Tue, 18 Sep 2012 16:58:54 GMTboehnchttp://www.sqlservercentral.com/Forums/Topic1360536-1292-1.aspx[quote][b]boehnc (9/17/2012)[/b][hr]this is basically the code...this join... left join UserView.vwEncounterDiagnosis d on d.VisitId=a.VisitId...will give me multiple rows per patienti might need help with the diagnosis order/code statements in the where code as well?[/quote]If you're not going to be able to extrapolate from psuedocode, we're going to need a lot more details from you to provide you a working example.Please see the first link in my signature for what we'll require to properly setup working code for your example and to show you exactly what you'll need to do. This includes schema for test structures, sample data, and expected results from that data, in a format we can cut and paste into our own servers and after review run scripts against what they create.Tue, 18 Sep 2012 13:12:39 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1360536-1292-1.aspx[quote][b]boehnc (9/17/2012)[/b][hr]this is basically the code...this join... left join UserView.vwEncounterDiagnosis d on d.VisitId=a.VisitId...will give me multiple rows per patienti might need help with the diagnosis order/code statements in the where code as well?select a.EncounterKey,a.DivisionName, a.VisitId,a.MedicalRecordNumber,a.InOutIndicator,a.EDFlag,a.AgeYear,a.PatientGender, a.ChargeTotal,pa.City,pa.State,a.PatientZipfrom UserView.vwAccount aleft join UserView.vwPayer p on p.PayerPlanKey = a.PayerPrimaryKeyleft join UserView.vwPatient pa on pa.PatientKey=a.PatientKeyleft join UserView.vwEncounterDiagnosis d on d.VisitId=a.VisitIdwhere a.DischargeDate>= '2011-01-01' and a.DischargeDate<='2012-01-01'and p.PayerClassGroupName in ('blue cross','true blue')and a.ChargeTotal>0and ((d.DiagnosisOrder between '1' and '4' and d.DiagnosisCode between '140.0' and '239.99')or (d.DiagnosisOrder between '1' and '4' and d.DiagnosisCode between 'v10.00' and 'v10.99')or (d.DiagnosisOrder between '1' and '4' and d.DiagnosisCode between 'v76.0' and 'v76.99')or (d.DiagnosisOrder between '1' and '4' and d.DiagnosisCode in ('v66.1','v66.2','v67.1','v67.2','v58.42')))[/quote]Did you try the pseudo code posted by Kraig ? or try with DISTINCT.Tue, 18 Sep 2012 01:01:50 GMTrhythmkhttp://www.sqlservercentral.com/Forums/Topic1360536-1292-1.aspxthis is basically the code...this join... left join UserView.vwEncounterDiagnosis d on d.VisitId=a.VisitId...will give me multiple rows per patienti might need help with the diagnosis order/code statements in the where code as well?select a.EncounterKey,a.DivisionName, a.VisitId,a.MedicalRecordNumber,a.InOutIndicator,a.EDFlag,a.AgeYear,a.PatientGender, a.ChargeTotal,pa.City,pa.State,a.PatientZipfrom UserView.vwAccount aleft join UserView.vwPayer p on p.PayerPlanKey = a.PayerPrimaryKeyleft join UserView.vwPatient pa on pa.PatientKey=a.PatientKeyleft join UserView.vwEncounterDiagnosis d on d.VisitId=a.VisitIdwhere a.DischargeDate>= '2011-01-01' and a.DischargeDate<='2012-01-01'and p.PayerClassGroupName in ('blue cross','true blue')and a.ChargeTotal>0and ((d.DiagnosisOrder between '1' and '4' and d.DiagnosisCode between '140.0' and '239.99')or (d.DiagnosisOrder between '1' and '4' and d.DiagnosisCode between 'v10.00' and 'v10.99')or (d.DiagnosisOrder between '1' and '4' and d.DiagnosisCode between 'v76.0' and 'v76.99')or (d.DiagnosisOrder between '1' and '4' and d.DiagnosisCode in ('v66.1','v66.2','v67.1','v67.2','v58.42')))Mon, 17 Sep 2012 22:47:17 GMTboehnchttp://www.sqlservercentral.com/Forums/Topic1360536-1292-1.aspx[quote][b]boehnc (9/17/2012)[/b][hr]I have one table "patients"...one row per patient.Patient.ID, Patient.Name, Patient.address, Patient.gender1/Jim/123 elm/MI have another table of patient diagnosis codes. It can have multiple dx codes per patient.dx.patientid/dx.dxcode/dx.dxcodedesc1/23/diabetes1/35/chf2/33/pnem2/34/stroke2/56/headacheI need to bring back in a table unique patients where dxcode = 23 or 34.one patient can have 23 and 34..I just need to bring back whether they had one or the other. If I do a left outer join, a patient with 23 and 34 will bring back two entries. How do I have it bring back just one unique entry? Thanks[/quote]Left outer join !!Can you please post your query with expected output.Mon, 17 Sep 2012 22:22:57 GMTrhythmkhttp://www.sqlservercentral.com/Forums/Topic1360536-1292-1.aspx[quote][b]boehnc (9/17/2012)[/b][hr]I need to bring back in a table unique patients where dxcode = 23 or 34.one patient can have 23 and 34..I just need to bring back whether they had one or the other. If I do a left outer join, a patient with 23 and 34 will bring back two entries. How do I have it bring back just one unique entry? Thanks[/quote]If you setup schema and data, we'll be able to provide you actual code, but in the meantime, psuedocode will have to do:[code="sql"]SELECTmt.ID, MAX( c.dxCode)FROM MainTable AS mtJOINdxcodes AS cON mt.Id = c.IdGROUP BYmt.ID[/code]Mon, 17 Sep 2012 21:36:28 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1360536-1292-1.aspxI have one table "patients"...one row per patient.Patient.ID, Patient.Name, Patient.address, Patient.gender1/Jim/123 elm/MI have another table of patient diagnosis codes. It can have multiple dx codes per patient.dx.patientid/dx.dxcode/dx.dxcodedesc1/23/diabetes1/35/chf2/33/pnem2/34/stroke2/56/headacheI need to bring back in a table unique patients where dxcode = 23 or 34.one patient can have 23 and 34..I just need to bring back whether they had one or the other. If I do a left outer join, a patient with 23 and 34 will bring back two entries. How do I have it bring back just one unique entry? ThanksMon, 17 Sep 2012 21:15:46 GMTboehnc