mysql find jobs in deptno 1 that are also in deptno 3-CodePudding

empno	ename	job	mgr	hiredate	sal	comm	deptno
7369	Smith	Clerk	7902	1980-12-17 00:00:00	800		2
7499	Allen	Salesman	7698	1981-02-20 00:00:00	1600	300	3
7521	Ward	Salesman	7698	0000-00-00 00:00:00	1250	500	3
7566	Jones	Manager	7839	0000-00-00 00:00:00	2975		2
7654	Martin	Salesman	7698	0000-00-00 00:00:00	1250	1400	3
7698	Blake	Manager	7839	0000-00-00 00:00:00	2850		3
7782	Clark	Manager	7839	0000-00-00 00:00:00	2450		1
7788	Scott	Analyst	7566	0000-00-00 00:00:00	3000		2
7839	King	President		0000-00-00 00:00:00	5000		1
7844	Turner	Salesman	7698	0000-00-00 00:00:00	1500	0	3
7876	Adams	Clerk	7788	0000-00-00 00:00:00	1100		2
7900	James	Clerk	7698	0000-00-00 00:00:00	950		3
7902	Ford	Analyst	7566	0000-00-00 00:00:00	3000		2
7934	Miller	Clerk	7782	0000-00-00 00:00:00	1300		1

I have this table. Im looking to use sub-query to find employees in deptno1 with the same positions as in deptno3

So a realistic output should look like this:

ename	job
Clark	Manager
Miller	Clerk

I'm new to mysql, but i havent had any real difficulty until i reached subquery question, so if someone could briefly explain what it is, and how i can effectively use it, I would appreciate it very much.

This was my attempt at solving, but it doesnt work:

select ename, job from emp where deptno = (select deptno from emp where deptno = 1);

CodePudding user response：

You could use a self join

select t1.ename, t1.job 
from emp t1 
inner join emp t2 on t1.job = t2.job 
  and t1.deptno = 1 
  and t2.deptno = 3

CodePudding user response：

Perhaps your query would be better expressed as

select ename, job 
from emp 
where depno = 1 and job in (select job from emp where deptno = 3);

CodePudding user response：

This query:

SELECT job FROM emp WHERE deptno = 3

returns all the jobs in deptno = 3 and you should use it in the WHERE clause of your query with the operator IN instead of =:

SELECT ename, job 
FROM emp 
WHERE deptno = 1
  AND job IN (SELECT job FROM emp WHERE deptno = 3);