[SOLVED] Find all departments which have engineers AND analysts. Use Join and don't forget about the NULL values

Find all departments which have engineers AND analysts. Use Join and don't forget about the NULL values

I'm having problem getting the right solution on the asked question. I have 3 tables: employees, departement, salgrade. This is what i got so far:

SELECT e0.deptno
FROM EMP AS e0
INNER JOIN dept AS d0 ON e0.deptno=d0.deptno
WHERE
e0.job ='Engineer'
AND
e0.job='Analyst'
AND 
e0.job IS NOT NULL;

The expected result is the number of the department, but my result doesn't show anything. I tried using an INTERSECT operator and everything works fine. But I don't understand what I'm doing wrong when using JOIN. I think I'm not doing something correctly with the NULL values. Can somebody try to explain me? Thanks

Solution

If you have to use a join then:

SELECT d0.deptno
FROM dept AS d0 INNER JOIN EMP AS e0 
ON e0.deptno = d0.deptno
WHERE e0.job IN ('Engineer', 'Analyst')
GROUP BY d0.deptno
HAVING COUNT(DISTINCT e0.job) = 2;

But all the info you want is there in the table EMP, so you can do it without the join:

SELECT deptno
FROM EMP 
WHERE job IN ('Engineer', 'Analyst')
GROUP BY deptno
HAVING COUNT(DISTINCT job) = 2;