SQL Query Help - 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

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Query Help

Author

Topic

IK1972

56 Posts

Posted - 2012-08-22 : 11:11:16

I have dataset like this

CaseID, PID
A1, 1
A1, 2
A1, 3
A1, 4
A1, 5
A2, 3
A2, 4
A2, 5
A2, 6
A2, 7
A3, 1
A3, 3
A3, 4
A3, 5

Then I have PIDs like 1,4,5 and I need to get all CaseID there this PID not exists. If any one PID exists for Case Number then I don't need that Case ID if any PID not exists then I want that CaseID.

Thanks

theboyholty
Posting Yak Master

226 Posts

Posted - 2012-08-22 : 11:29:50

You haven't exactly made yourself clear, but my best guess is that you want CaseID's where there are no occurrences of a specific PID.

try this:

declare @pid int
set @pid = 2

select CaseID
from YourTable yt
where not exists (select CaseID from YourTable ne where PID = @PID and ne.caseID=yt.CaseID)
GROUP BY CaseID

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 12:15:33

quote:
Originally posted by inayat1972

I have dataset like this

CaseID, PID
A1, 1
A1, 2
A1, 3
A1, 4
A1, 5
A2, 3
A2, 4
A2, 5
A2, 6
A2, 7
A3, 1
A3, 3
A3, 4
A3, 5

Then I have PIDs like 1,4,5 and I need to get all CaseID there this PID not exists. If any one PID exists for Case Number then I don't need that Case ID if any PID not exists then I want that CaseID.

Thanks


SELECT CaseID
FROM table
GROUP BY CaseID
HAVING SUM(CASE WHEN PID IN (1,4,5) THEN 1 ELSE 0 END)=0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

IK1972

56 Posts

Posted - 2012-08-23 : 11:47:10

[visakh16]

My PID is basically 5 number integer like 25489, 254795, 45856 and I have very large data set.

Is this work with large data and with PID i mentioned above. If its work then I like this its more simple and clean as compare to check with Exists or not Exists other options I check.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-23 : 12:06:59

quote:
Originally posted by inayat1972

[visakh16]

My PID is basically 5 number integer like 25489, 254795, 45856 and I have very large data set.

Is this work with large data and with PID i mentioned above. If its work then I like this its more simple and clean as compare to check with Exists or not Exists other options I check.

Thanks

it should still work fine
why not test it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources