| 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);
>Solution :
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);