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...
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');
David Miller brings 15 years of experience in global economics, personal finance strategy, and market dynamics. He specializes in turning complex economic trends into actionable insights for everyday readers.
