very simple query - 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

very simple query

Author

Topic

boehnc
Starting Member

15 Posts

Posted - 2011-07-07 : 15:42:17

I have 3 columns

Column 1 = Visit ID
Column 2 = Medical Record number
Column 3 = Diagnosis Code

Each patient has one Visit ID and Medical Record Number but has 10 Diagnosis codes...so, Visit ID and MRN are recurring, but has 10 unique Diagnosis codes.

I'm trying to write a query that will bring back all patients that have both dx code 550 AND 4400.

I know it's simple but my DiagnosisCode in ('550', 4400)is bringing back one or the other.

Any help is much appreciated!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-07 : 17:47:38

One way to do it is as follows:


select * from YourTable t1
where
exists (
   Select * from YourTable t2 
   where t1.visitId = t2.visitId and t1.MRN = t2.MRN and t2.DiagCod = 550)
and exists(
   select * from YourTable t3
   where t1.visitId = t23.visitId and t1.MRN = t3.MRN and t3.DiagCod = 4400)

You can also do this using joins - by joining to the same table 3 times on appropriate conditions.

sshelper
Posting Yak Master

216 Posts

Posted - 2011-07-08 : 01:03:18

Try this one:

SELECT
FROM [dbo].[YourTable] A INNER JOIN [dbo].[YourTable] B
ON A.[VisitID] = B.[VisitID] AND A.[MRN] = B.[MRN] AND A.[DiagnosisCode] = 550 AND B.[DiagnosisCode] = 4400

Regards,
SQL Server Helper
http://www.sql-server-helper.com/functions/date-and-time-functions/index.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources