CODEWITHSUNDEEP
sql-server
Sameep Baxi
Sameep Baxi

Reputation: 111

Selecting rows which does not have particular values against the values of another column

My query is like:

I have a table Table1

     SkillID            EmpCode
    ------------------------------
    2                   A101
    4                   A101
    5                   A101
    8                   A101
    4                   A102
    3                   A102
    1                   A102
    6                   A102
    5                   A102
    7                   A102

Now I have to SELECT EmpCode which does not have SkillIDs 1, 6, & 7. Here in the example it should give EmpCode A101 as the result. Only EmpCodes which doesn't include SkillID 1, 6 & 7 should be the result

Upvotes: 0

Views: 33

Answers (2)

t1t1an0
t1t1an0

Reputation: 281

the code below is in t-sql:

SELECT DISTINCT EmpCode
FROM Table1
WHERE SkillID NOT IN (1, 6, 7)

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try:

SELECT EmpCode
FROM mytable
GROUP BY EmpCode
HAVING COUNT(CASE WHEN SkillID IN (1, 6, 7) THEN 1 END) = 0

Demo here

You can alternatively use NOT EXISTS:

SELECT DISTINCT EmpCode
FROM mytable AS t1
WHERE NOT EXISTS (
  SELECT 1
  FROM mytable AS t2
  WHERE t1.EmpCode = t2.EmpCode AND t2.SkillID IN (1, 6, 7) )

Demo here

Upvotes: 2

Related Questions