CODEWITHSUNDEEP
mysqlsqlselect
Pi_
Pi_

Reputation: 2110

MySQL SELECT statement to retrieve field value that is unique

For example

id   staff_id   skill_id   mainskill
1    1          24         1
2    1          24         0 
3    1          7          0
4    4          24         0
5    4          18         0
6    6          8          0
7    6          18         1

I would like the result to contain only the tuples with a skill_id that is present only once in all the data. In other words I want to retrieve the tuples containing the skill_ids that are only possessed by a single staff member.

And so the desired output is:

id   staff_id   skill_id   mainskill
3    1          7          0
6    6          8          0

Thanks in advance :).

Upvotes: 1

Views: 61

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

You can do it with GROUP BY and HAVING, like this:

SELECT 
  MAX(id) as id,
  MAX(staff_id) as staff_id, 
  skill_id, 
  MAX(mainskill) as mainskill
FROM MyTable
GROUP BY skill_id
HAVING COUNT(1)=1

Upvotes: 3

Related Questions