CODEWITHSUNDEEP
sql
Jes Gudiksen
Jes Gudiksen

Reputation: 452

SQL select Distinct with where clause

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 ?

Upvotes: 5

Views: 36024

Answers (5)

Grisha Weintraub
Grisha Weintraub

Reputation: 7986

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

Upvotes: 3

Magnus
Magnus

Reputation: 46957

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
    )

Upvotes: 3

thenna
thenna

Reputation: 71

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

Upvotes: -1

John Woo
John Woo

Reputation: 263763

Try this,

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

SQLFiddle Demo

Upvotes: 9

Niladri Biswas
Niladri Biswas

Reputation: 4171

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

Upvotes: 4

Related Questions