I have i table like this.

PersonID, KvalifikationId 1 1 1 2 1 3 2 1 2 3 

I want to write SQL querye returning all persons, that have not kvalifikation 2.

i Wrote

SELECT DISTINCT PersonID where NOT KvalifikationID = 2 

But this return both person 1 and person 2. How do i make select that only return personId's that not have kval2 ?

5 Answers

Try this,

SELECT DISTINCT PersonID FROM tableName WHERE PersonID NOT IN ( SELECT PersonID FROM tableName WHERE KvalifikationId = 2 ) 

SQLFiddle Demo

Declare @t table(PersonID int,KvalifikationId int) Insert Into @t Select 1 ,1 Insert Into @t Select 1, 2 Insert Into @t Select 1,3 Insert Into @t Select 2 ,1 Insert Into @t Select 2,3 Select PersonId From @t Except Select PersonID From @t where KvalifikationId = 2 

Result

PersonId 2 

By using your Person table rather than your N:N table in the outer query you can skip the distinct and the anti semi join to the sub query will have better performance since it is on a clustered index. (assuming PersonID is pk in the Person table)

SELECT PersonID FROM tblPerson WHERE NOT EXISTS ( SELECT NULL FROM tblPersonKvalifikation WHERE KvalifikationId = 2 AND tblPerson.PersonID = tblPersonKvalifikation.PersonID ) 

SELECT DISTINCT person_id FROM tableName t1 WHERE not exists ( select 1 from tableName where person_id = t1.person_id and KvalifikationId = 2 ) 

try this.
SELECT DISTINCT PersonID from tableName
WHERE KvalifikationId NOT IN ('2');