Delete both based on one condition

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)

Delete both based on one condition

Author

Topic

omega1983
Starting Member

40 Posts

Posted - 2009-11-11 : 10:09:36

Here is an output of some data. Note in the first instance I want to eliminate John Smith because of the presence of SECONDARYDEPTID 14. Eliminating Phil Jones is the easy part. Eliminating John Smith is difficult because SQL evaluates each line individually so his entry 14 is eliminated but he still shows up under #15

ID NAME DEPT SECONDARYDEPTID
1 John Smith ENGINEERING 14
1 John Smith ENGINEERING 15
----------------I want to eliminate both-----------------
2 Sam Brown ENGINEERING 15
3 Louise Brown ENGINEERING 15
----------------------I want to eliminate Phil Jones------
5 Phil Jones ENGINEERING 14

select ID,NAME,DEPT,SECONDARYDEPT
from myTable where
SECONDARYDEPT not = '14'

this query will not eliminate both John Smith's entry 14 and 15. How can I eliminate both in this case

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-11 : 10:16:42

eliminate once there is a 14 ?


select ID, NAME, DEPT, SECONDARYDEPT
from   myTable t
where  not exists
       (
           select *
           from   myTable x
           where  x.ID = t.ID
           and    x.SECONDARYDEPT = '14'
       )

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources