CODEWITHSUNDEEP
sqlms-access
Jazzmine
Jazzmine

Reputation: 1875

SQL statement to find a key that doesn't contain a row with a value

I have a table that has multiple rows per key (employee #) as shown below. I'm trying to find keys that do have particular skills across their set of rows.

I need help to write an Access SQL query that only identifies employees that do have Project Management and Speaking skill records. I don't care if they do have a Coding record.

Employee #       Skill
5261            Coding
8328            Coding
8328            Speaking
8328            Project Management

Employee 5261 should be excluded in the output because that employee does not have a Project Management and Speaking skill listed. Employee 8328 should be included in the output because that employee has Project Management and Speaking skill.

Upvotes: 0

Views: 85

Answers (2)

Syeda
Syeda

Reputation: 1205

You can use below query for the desired output

SELECT [Employee #] as Employee, count(*) AS cnt
FROM sourcetable
WHERE skill IN ('Speaking', 'Project Management')
GROUP BY [Employee #]
HAVING cnt > 1

Upvotes: 0

Marc B
Marc B

Reputation: 360762

just do a basic where filter for both skills, but count how many of the skills the person has, and filter on that count:

SELECT [Employee #], count(*) AS cnt
FROM yourtable
WHERE Skill IN ('Speaking', 'Project Management')
GROUP BY [Employee #]
HAVING cnt = 2

Upvotes: 2

Related Questions