not exist from same table - 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 2000 Forums

Transact-SQL (2000)

not exist from same table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-24 : 23:12:02

Leon writes "I need to be able to retrieve rows of data for one ID and compare it to another of another ID in the same table. Where it dumps rows(of the first set) which are duplicated in the second group.

what i have tried is this

SELECT SpeciesHeld.Year, SpeciesTypeCode, FarmedOrHeld, DevelopmentCode FROM SpeciesHeld Where FarmPersonID = '10'
and not exists (SELECT SpeciesHeld.Year, SpeciesTypeCode, FarmedOrHeld, DevelopmentCode FROM SpeciesHeld Where FarmPersonID = '2239')

I hope you understand this as i'm finding it difficult to explain.

So i want to get rid of rows from the outer select which have the same values as the select clauses fields."

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-25 : 13:59:56

several ways - easiest probably

SELECT SpeciesHeld.Year, SpeciesTypeCode, FarmedOrHeld, DevelopmentCode
FROM SpeciesHeld s1
Where FarmPersonID = '10'
and not exists
(
SELECT *
from SpeciesHeld s2
where s1.SpeciesHeld.Year = s2.SpeciesHeld.Year
and s1.SpeciesTypeCode = s2.SpeciesTypeCode
and s1.FarmedOrHeld = s2.FarmedOrHeld
and s1.DevelopmentCode = s2.DevelopmentCode
and s2.FarmPersonID = '2239'
)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Leonus
Starting Member

1 Post

Posted - 2002-08-28 : 04:46:57

Thanks nr that worked great

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources