SQL query for simple table

Question

I have a table:

 ( EmployeeID (int), CourseID (int))

It is 1 to many relation. Employee can have many courseID.

EmployeeID         
   1                  
   1                  
   2                  
   2                  
   3                  
   3                  
   3       

CourseID            
   1                  
   2                  
   2                  
   3                  
   1                  
   2                  
   3      

for example I would like to know number of employee who has courseID 1 and 2

Select  COUNT([employee ID])
From EmployeeTable
Where courseID = 1 and courseID = 2

Answer should be 2 because only EmployeeID 1 and EmployeeID 3 has CourseID 1 and 2.

Answer 1

Here's a fast way using count distinct to select and count all employees that have both courses 1 and 2

select count(*) from (
    select EmployeeID
    from EmployeeTable
    where CourseID in (1,2)
    group by EmployeeID
    having count(distinct CourseID) = 2
) t

Answer 2

The WHERE clause is evaluated for each row returned by your FROM clause. As you have it, the WHERE clause is checking if Course ID has 2 different values, this will never evaluate to true. A solution I see is using a self join.

SELECT COUNT(*) FROM EmployeeTable AS ET1 JOIN EmployeeTable AS ET2 ON ET1.[employee ID] = ET2.[employeeID] AND ET1.[course 
ID] = 1 AND ET2.[course ID] = 2

This looks like a join table. If you had a separate employee table you could do something easier with a subquery.

Answer 3

This is not so simple... your attempt will return 0 because no single row has both CourseID = 1 AND CourseID = 2.

You can do it using subqueries:

SELECT COUNT(*) 
FROM EmployeeTable e1
WHERE EXISTS
(
    SELECT *
    FROM EmployeeTable e2
    WHERE e2.CourseID = 1
    AND e2.EmployeeID = e1.EmployeeID
)
AND EXISTS
(
    SELECT *
    FROM EmployeeTable e3
    WHERE e3.CourseID = 2
    AND e3.EmployeeID = e1.EmployeeID
)