Find records in one but not another query

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

SQL Server 2005 Forums

Transact-SQL (2005)

Find records in one but not another query

Author

Topic

LGOOLSBY
Starting Member

4 Posts

Posted - 2007-03-28 : 10:38:22

Simple but somewhat new and so I am asking...

I have a large table. Fields in this table include SimID, PatientID, ContCode. I want to locate all records with SimID of 204 where a corresponding record with SimID of 205 does not exists. The matching fields for the different SimIDs is PatientID & ContCode. So if a PatientID 123 and ContCode 99213 does not exist with SimID205 but does in 204 then I want to see it.

Did I say that right, I hope so.

Thanks,
Lee

LGOOLSBY
Starting Member

4 Posts

Posted - 2007-03-28 : 10:49:07

Thanks but the values I gave were examples. I will not know which recrods (PatientID and ContCodes) do not exist. I need to query to find that information.

Lee

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-28 : 10:52:24

[code]Select patientId, ContCode
from tbl
Where SimID in (204, 205)
group by patientId, ContCode
having count(SimID) = 1[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 10:52:36

You better post some sample data and expected output.

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-28 : 11:03:10

OK, this should be right now

SELECT T1.* 
FROM yourtable T1
LEFT OUTER JOIN yourtable T2 
    ON T1.PatientID = T2.PatientID and T1.ContCode = T2.ContCode and T1.SimID = 204 and T2.SimID = 205
WHERE T1.SimID = 204 AND T2.SimID IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources