CODEWITHSUNDEEP
mysqlsql
user3797088
user3797088

Reputation: 563

MySQL - how to return a list of iD number from another table excluding a specific ID number

I'm making a performance evaluation system. This is my example database:

Table: Person
ID     Name
--     ---
1      James
2      John
3      Jake

Table: Evaluation
-------  --  ------------
Eval_ID  ID  Evaluator_ID
0        1       2

I need a query where I can find a list of ID number from person that has not been evaluated by the evaluator_ID. So for example the ID number 1 would not be displayed from the result since it has been already evaluated by 2. So only the ID number 3 will be displayed because you cannot evaluate yourself.

Upvotes: 1

Views: 51

Answers (2)

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

SELECT p.ID, p.Name
FROM Person p
WHERE p.ID NOT in (SELECT e.ID FROM Evaluation e group by e.id )
 and   p.ID NOT in (SELECT f.Evaluator_ID FROM Evaluation f group by f.Evaluator_ID )

Upvotes: 0

Matt
Matt

Reputation: 15071

Use NOT EXISTS

SELECT p.ID, p.Person
FROM Person p
WHERE p.ID NOT EXISTS (SELECT e.ID FROM Evaluation e WHERE p.ID = e.ID OR p.ID = e.Evaluator_ID)

Upvotes: 1

Related Questions