Geek asks and answers

SQL select Distinct with where clause

I have a table like this.

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

I want to write an SQL query that returns all persons who do not have kvalifikation 2.

I wrote

SELECT DISTINCT PersonID where NOT KvalifikationID = 2

But this returns both person 1 and person 2. How to make a choice that only returns personId that does not have kval2?

+5
source share
5 answers

Try it,

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

SQLFiddle Demo

+7
source 
SELECT DISTINCT person_id
FROM tableName t1
WHERE not exists 
(
  select 1 
  from tableName 
  where person_id = t1.person_id and KvalifikationId = 2 
)
+3
source 
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


PersonId
2
+3

Person, N: N , distinct, -- , . (, PersonID pk Person)

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

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

-1
source

More articles:


All Articles