i need to display a list of employees who do not have a designated supervisor working in the same department
Table: employee
Fields:
id INT(pk),
department_id INT,
chief_id INT,
name Varchar(100),
salary INT
Query:
SELECT One.name AS Employee,One.department_id, Two.name AS Chief, Two.department_id
FROM employee One, employee Two
WHERE (One.chief_id = Two.id AND One.department_id != Two.department_id) OR One.department_id IS NULL;
SELECT One.name AS Employee,One.department_id, Two.name AS Chief, Two.department_id
FROM employee One
LEFT JOIN employee two ON One.chief_id = Two.id
WHERE NOT EXISTS (
SELECT 0 FROM employee two
where One.department_id = Two.department_id
AND One.chief_id = Two.id
)
Try following;)
SELECT One.name AS Employee,One.department_id, Two.name AS Chief, Two.department_id
FROM employee One, employee Two
WHERE One.chief_id = Two.id AND (One.department_id IS NULL OR One.department_id <> Two.department_id);
Below query might give the required result :-
SQL SERVER :-
SELECT One.name AS Employee,One.department_id, Two.name AS Chief, Two.department_id
FROM employee One, employee Two
WHERE (One.chief_id = Two.id AND One.department_id != Two.department_id) OR ISNULL(One.department_id,0)=0;
MYSQL :-
SELECT One.name AS Employee,One.department_id, Two.name AS Chief, Two.department_id
FROM employee One, employee Two
WHERE (One.chief_id = Two.id AND One.department_id != Two.department_id) OR IFNULL(One.department_id,0)=0;
Try the query
SELECT
Two.name AS Employee,
Two.department_id AS Employee_Dept,
One.name AS Chief_Name,
One.department_id AS Chif_Dept
FROM
employee ONE
INNER JOIN employee Two
ON One.id = Two.chief_id
AND One.department_id != Two.department_id ;
