I have a database table with employees (emp). I am trying to write a query to find employees (name) in department 1 (deptno=1) with the same jobs (job) as employees i department 2 (deptno=2). In other words, the results should be the name of employees in department 1 who has a job than can be found amongst one or more of the employees in department 2.

I tried the following code, but it did not work. Any ideas?

select name from emp where deptno=1 and job=(select job from emp where deptno=3)

CodePudding user response：

If there is any possibilities of having multiple job under single department then you can use IN :

select name from emp where deptno=1 
and job in (select job from emp where deptno=2)