Anuar  Maratkhan Anuar Maratkhan - 1 year ago 120
SQL Question

SQL Query: Employee Database

I have such SQL Database where there are employees and supervisors assigned to them. For supervisors who have no male employees working for them, I need to find the SSN and the total number of employees working for them.

This is how my table in database looks: enter image description here

And here is my code for the solution:

SELECT supervisors.ssn, COUNT(e.ssn)
FROM employee AS supervisors, employee AS e
WHERE e.ssn NOT IN (SELECT e.ssn FROM employee e WHERE = 'M') AND supervisors.ssn = e.superssn
GROUP BY supervisors.ssn;

But the code above finds the supervisors who has at least one Female employee. However, I need to find supervisors who have only Female employees.

Answer Source

Many of the Answers will exclude results if the Supervisor is Male

This will not

SELECT employee.lname, count(employee.lname) as NumberOfEmployees 
FROM Employee

SELECT Employee.SuperSSN
FROM Employee
WHERE Employee.SuperSSN NOT IN
(SELECT Employee.SuperSSN FROM Employee e
ON e.SSN = Employee.SuperSSN
WHERE Employee.SEX = 'M')
AS e
ON Employee.ssn = e.superssn
GROUP BY employee.lname
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download