Write a query to show ename who don't have reporting manager in deptno 10 or 30?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
-1
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 2900 20
7499 ALLEN SALESMAN 7698 20-FEB-81 3600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 3250 500 30
7566 JONES MANAGER 7839 02-APR-81 4975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 3250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 4850 30
7782 CLARK MANAGER 7839 09-JUN-81 4450 10
7788 SCOTT ANALYST 7566 19-APR-87 5000 20
7839 KING PRESIDENT 17-NOV-81 7000 10
7844 TURNER SALESMAN 7698 08-SEP-81 3500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 3100 20
7900 JAMES CLERK 7698 03-DEC-81 2950 30
7902 FORD ANALYST 7566 03-DEC-81 5000 20
7934 MILLER CLERK 7782 23-JAN-82 3300 10
This is the emp table given in Oracle SQL.
And I need to display the employees who don't have reporting manager in department 10 or 30.
sql oracle oracle11g
edited Nov 27 '18 at 4:06
Barbaros Özhan
15k71634
asked Nov 26 '18 at 20:32
Alok PadhiAlok Padhi
31
add a comment |
-1
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 2900 20
7499 ALLEN SALESMAN 7698 20-FEB-81 3600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 3250 500 30
7566 JONES MANAGER 7839 02-APR-81 4975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 3250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 4850 30
7782 CLARK MANAGER 7839 09-JUN-81 4450 10
7788 SCOTT ANALYST 7566 19-APR-87 5000 20
7839 KING PRESIDENT 17-NOV-81 7000 10
7844 TURNER SALESMAN 7698 08-SEP-81 3500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 3100 20
7900 JAMES CLERK 7698 03-DEC-81 2950 30
7902 FORD ANALYST 7566 03-DEC-81 5000 20
7934 MILLER CLERK 7782 23-JAN-82 3300 10
This is the emp table given in Oracle SQL.
And I need to display the employees who don't have reporting manager in department 10 or 30.
sql oracle oracle11g
edited Nov 27 '18 at 4:06
Barbaros Özhan
15k71634
asked Nov 26 '18 at 20:32
Alok PadhiAlok Padhi
31
Hint:JOINandWHEREorNOT EXISTS.
– Gordon Linoff
Nov 26 '18 at 20:35
Are you hinting towards a self-join as there is no other manager table present?
– Alok Padhi
Nov 26 '18 at 20:36
add a comment |
-1
-1
-1
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 2900 20
7499 ALLEN SALESMAN 7698 20-FEB-81 3600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 3250 500 30
7566 JONES MANAGER 7839 02-APR-81 4975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 3250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 4850 30
7782 CLARK MANAGER 7839 09-JUN-81 4450 10
7788 SCOTT ANALYST 7566 19-APR-87 5000 20
7839 KING PRESIDENT 17-NOV-81 7000 10
7844 TURNER SALESMAN 7698 08-SEP-81 3500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 3100 20
7900 JAMES CLERK 7698 03-DEC-81 2950 30
7902 FORD ANALYST 7566 03-DEC-81 5000 20
7934 MILLER CLERK 7782 23-JAN-82 3300 10
This is the emp table given in Oracle SQL.
And I need to display the employees who don't have reporting manager in department 10 or 30.
sql oracle oracle11g
edited Nov 27 '18 at 4:06
Barbaros Özhan
15k71634
asked Nov 26 '18 at 20:32
Alok PadhiAlok Padhi
31
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 2900 20
7499 ALLEN SALESMAN 7698 20-FEB-81 3600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 3250 500 30
7566 JONES MANAGER 7839 02-APR-81 4975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 3250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 4850 30
7782 CLARK MANAGER 7839 09-JUN-81 4450 10
7788 SCOTT ANALYST 7566 19-APR-87 5000 20
7839 KING PRESIDENT 17-NOV-81 7000 10
7844 TURNER SALESMAN 7698 08-SEP-81 3500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 3100 20
7900 JAMES CLERK 7698 03-DEC-81 2950 30
7902 FORD ANALYST 7566 03-DEC-81 5000 20
7934 MILLER CLERK 7782 23-JAN-82 3300 10
This is the emp table given in Oracle SQL.
And I need to display the employees who don't have reporting manager in department 10 or 30.
sql oracle oracle11g
sql oracle oracle11g
edited Nov 27 '18 at 4:06
Barbaros Özhan
15k71634
asked Nov 26 '18 at 20:32
Alok PadhiAlok Padhi
31
edited Nov 27 '18 at 4:06
Barbaros Özhan
15k71634
asked Nov 26 '18 at 20:32
Alok PadhiAlok Padhi
31
edited Nov 27 '18 at 4:06
Barbaros Özhan
15k71634
edited Nov 27 '18 at 4:06
Barbaros Özhan
15k71634
edited Nov 27 '18 at 4:06
Barbaros Özhan
15k71634
15k71634
asked Nov 26 '18 at 20:32
Alok PadhiAlok Padhi
31
asked Nov 26 '18 at 20:32
Alok PadhiAlok Padhi
31
asked Nov 26 '18 at 20:32
Alok PadhiAlok Padhi
31
31
Hint:JOINandWHEREorNOT EXISTS.
– Gordon Linoff
Nov 26 '18 at 20:35
Are you hinting towards a self-join as there is no other manager table present?
– Alok Padhi
Nov 26 '18 at 20:36
add a comment |
Hint:JOINandWHEREorNOT EXISTS.
– Gordon Linoff
Nov 26 '18 at 20:35
Are you hinting towards a self-join as there is no other manager table present?
– Alok Padhi
Nov 26 '18 at 20:36
Hint:
JOIN and WHERE or NOT EXISTS.– Gordon Linoff
Nov 26 '18 at 20:35
Hint:
JOIN and WHERE or NOT EXISTS.– Gordon Linoff
Nov 26 '18 at 20:35
Are you hinting towards a self-join as there is no other manager table present?
– Alok Padhi
Nov 26 '18 at 20:36
Are you hinting towards a self-join as there is no other manager table present?
– Alok Padhi
Nov 26 '18 at 20:36
add a comment |
4 Answers
4
active
oldest
votes
-1
I would use not exists:
select e.*
from emp e
where not exists (select 1
from emp em
where em.empno = e.mgr and em.deptno in (10, 30)
);
answered Nov 26 '18 at 20:47
Gordon LinoffGordon Linoff
796k37318423
can you explain why the empno and mgr need to be equal ?
– Alok Padhi
Nov 26 '18 at 21:02
That is how you find the manager corresponding to a given employee.
– Gordon Linoff
Nov 26 '18 at 21:07
And what will be wrong if I wrote the query like:select ename from emp where (deptno in (10, 30) and mgr is null) and (deptno = 20);
– Alok Padhi
Nov 26 '18 at 21:08
add a comment |
0
You can use a self join via left outer join like below:
Select ename
from emp e1
left outer join emp e2 on e2.MGR = e1.EMPNO
where e2.MGR is null AND e1.DEPTNO in (10,30)
answered Nov 26 '18 at 20:37
Eray BalkanliEray Balkanli
4,58852347
add a comment |
0
You may use subquery with a correlated subquery as :
select ename
from
(
select e.ename, e.mgr,
( select mgr
from emp
where empno = e.mgr
and nvl(deptno,0) not in (10,30) ) mgr2
from emp e
)
where mgr is null
or mgr2 is not null;
ENAME
------
KING
SCOTT
FORD
SMITH
ADAMS
answered Nov 26 '18 at 21:20
Barbaros ÖzhanBarbaros Özhan
15k71634
add a comment |
0
I have used With Clause :
with emp_data as (select EMPNO,DEPTNO from emp)
select ENAME from emp e,emp_data ed
where e.mgr =ed.empno(+) and
(ed.deptno not in (10,30) or e.mgr is null);
ENAME
------
FORD
SCOTT
ADAMS
SMITH
KING
edited Nov 27 '18 at 9:38
Adrian W
1,97651522
answered Nov 27 '18 at 8:37
Prabodh JenaPrabodh Jena
12
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53488610%2fwrite-a-query-to-show-ename-who-dont-have-reporting-manager-in-deptno-10-or-30%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
-1
I would use not exists:
select e.*
from emp e
where not exists (select 1
from emp em
where em.empno = e.mgr and em.deptno in (10, 30)
);
answered Nov 26 '18 at 20:47
Gordon LinoffGordon Linoff
796k37318423
can you explain why the empno and mgr need to be equal ?
– Alok Padhi
Nov 26 '18 at 21:02
That is how you find the manager corresponding to a given employee.
– Gordon Linoff
Nov 26 '18 at 21:07
And what will be wrong if I wrote the query like:select ename from emp where (deptno in (10, 30) and mgr is null) and (deptno = 20);
– Alok Padhi
Nov 26 '18 at 21:08
add a comment |
-1
I would use not exists:
select e.*
from emp e
where not exists (select 1
from emp em
where em.empno = e.mgr and em.deptno in (10, 30)
);
answered Nov 26 '18 at 20:47
Gordon LinoffGordon Linoff
796k37318423
can you explain why the empno and mgr need to be equal ?
– Alok Padhi
Nov 26 '18 at 21:02
That is how you find the manager corresponding to a given employee.
– Gordon Linoff
Nov 26 '18 at 21:07
And what will be wrong if I wrote the query like:select ename from emp where (deptno in (10, 30) and mgr is null) and (deptno = 20);
– Alok Padhi
Nov 26 '18 at 21:08
add a comment |
-1
-1
-1
I would use not exists:
select e.*
from emp e
where not exists (select 1
from emp em
where em.empno = e.mgr and em.deptno in (10, 30)
);
answered Nov 26 '18 at 20:47
Gordon LinoffGordon Linoff
796k37318423
I would use not exists:
select e.*
from emp e
where not exists (select 1
from emp em
where em.empno = e.mgr and em.deptno in (10, 30)
);
answered Nov 26 '18 at 20:47
Gordon LinoffGordon Linoff
796k37318423
answered Nov 26 '18 at 20:47
Gordon LinoffGordon Linoff
796k37318423
answered Nov 26 '18 at 20:47
Gordon LinoffGordon Linoff
796k37318423
answered Nov 26 '18 at 20:47
Gordon LinoffGordon Linoff
796k37318423
796k37318423
can you explain why the empno and mgr need to be equal ?
– Alok Padhi
Nov 26 '18 at 21:02
That is how you find the manager corresponding to a given employee.
– Gordon Linoff
Nov 26 '18 at 21:07
And what will be wrong if I wrote the query like:select ename from emp where (deptno in (10, 30) and mgr is null) and (deptno = 20);
– Alok Padhi
Nov 26 '18 at 21:08
add a comment |
can you explain why the empno and mgr need to be equal ?
– Alok Padhi
Nov 26 '18 at 21:02
That is how you find the manager corresponding to a given employee.
– Gordon Linoff
Nov 26 '18 at 21:07
And what will be wrong if I wrote the query like:select ename from emp where (deptno in (10, 30) and mgr is null) and (deptno = 20);
– Alok Padhi
Nov 26 '18 at 21:08
can you explain why the empno and mgr need to be equal ?
– Alok Padhi
Nov 26 '18 at 21:02
can you explain why the empno and mgr need to be equal ?
– Alok Padhi
Nov 26 '18 at 21:02
That is how you find the manager corresponding to a given employee.
– Gordon Linoff
Nov 26 '18 at 21:07
That is how you find the manager corresponding to a given employee.
– Gordon Linoff
Nov 26 '18 at 21:07
And what will be wrong if I wrote the query like:
select ename from emp where (deptno in (10, 30) and mgr is null) and (deptno = 20);– Alok Padhi
Nov 26 '18 at 21:08
And what will be wrong if I wrote the query like:
select ename from emp where (deptno in (10, 30) and mgr is null) and (deptno = 20);– Alok Padhi
Nov 26 '18 at 21:08
add a comment |
0
You can use a self join via left outer join like below:
Select ename
from emp e1
left outer join emp e2 on e2.MGR = e1.EMPNO
where e2.MGR is null AND e1.DEPTNO in (10,30)
answered Nov 26 '18 at 20:37
Eray BalkanliEray Balkanli
4,58852347
add a comment |
0
You can use a self join via left outer join like below:
Select ename
from emp e1
left outer join emp e2 on e2.MGR = e1.EMPNO
where e2.MGR is null AND e1.DEPTNO in (10,30)
answered Nov 26 '18 at 20:37
Eray BalkanliEray Balkanli
4,58852347
add a comment |
0
0
0
You can use a self join via left outer join like below:
Select ename
from emp e1
left outer join emp e2 on e2.MGR = e1.EMPNO
where e2.MGR is null AND e1.DEPTNO in (10,30)
answered Nov 26 '18 at 20:37
Eray BalkanliEray Balkanli
4,58852347
You can use a self join via left outer join like below:
Select ename
from emp e1
left outer join emp e2 on e2.MGR = e1.EMPNO
where e2.MGR is null AND e1.DEPTNO in (10,30)
answered Nov 26 '18 at 20:37
Eray BalkanliEray Balkanli
4,58852347
answered Nov 26 '18 at 20:37
Eray BalkanliEray Balkanli
4,58852347
answered Nov 26 '18 at 20:37
Eray BalkanliEray Balkanli
4,58852347
answered Nov 26 '18 at 20:37
Eray BalkanliEray Balkanli
4,58852347
4,58852347
add a comment |
add a comment |
0
You may use subquery with a correlated subquery as :
select ename
from
(
select e.ename, e.mgr,
( select mgr
from emp
where empno = e.mgr
and nvl(deptno,0) not in (10,30) ) mgr2
from emp e
)
where mgr is null
or mgr2 is not null;
ENAME
------
KING
SCOTT
FORD
SMITH
ADAMS
answered Nov 26 '18 at 21:20
Barbaros ÖzhanBarbaros Özhan
15k71634
add a comment |
0
You may use subquery with a correlated subquery as :
select ename
from
(
select e.ename, e.mgr,
( select mgr
from emp
where empno = e.mgr
and nvl(deptno,0) not in (10,30) ) mgr2
from emp e
)
where mgr is null
or mgr2 is not null;
ENAME
------
KING
SCOTT
FORD
SMITH
ADAMS
answered Nov 26 '18 at 21:20
Barbaros ÖzhanBarbaros Özhan
15k71634
add a comment |
0
0
0
You may use subquery with a correlated subquery as :
select ename
from
(
select e.ename, e.mgr,
( select mgr
from emp
where empno = e.mgr
and nvl(deptno,0) not in (10,30) ) mgr2
from emp e
)
where mgr is null
or mgr2 is not null;
ENAME
------
KING
SCOTT
FORD
SMITH
ADAMS
answered Nov 26 '18 at 21:20
Barbaros ÖzhanBarbaros Özhan
15k71634
You may use subquery with a correlated subquery as :
select ename
from
(
select e.ename, e.mgr,
( select mgr
from emp
where empno = e.mgr
and nvl(deptno,0) not in (10,30) ) mgr2
from emp e
)
where mgr is null
or mgr2 is not null;
ENAME
------
KING
SCOTT
FORD
SMITH
ADAMS
answered Nov 26 '18 at 21:20
Barbaros ÖzhanBarbaros Özhan
15k71634
answered Nov 26 '18 at 21:20
Barbaros ÖzhanBarbaros Özhan
15k71634
answered Nov 26 '18 at 21:20
Barbaros ÖzhanBarbaros Özhan
15k71634
answered Nov 26 '18 at 21:20
Barbaros ÖzhanBarbaros Özhan
15k71634
15k71634
add a comment |
add a comment |
0
I have used With Clause :
with emp_data as (select EMPNO,DEPTNO from emp)
select ENAME from emp e,emp_data ed
where e.mgr =ed.empno(+) and
(ed.deptno not in (10,30) or e.mgr is null);
ENAME
------
FORD
SCOTT
ADAMS
SMITH
KING
edited Nov 27 '18 at 9:38
Adrian W
1,97651522
answered Nov 27 '18 at 8:37
Prabodh JenaPrabodh Jena
12
add a comment |
0
I have used With Clause :
with emp_data as (select EMPNO,DEPTNO from emp)
select ENAME from emp e,emp_data ed
where e.mgr =ed.empno(+) and
(ed.deptno not in (10,30) or e.mgr is null);
ENAME
------
FORD
SCOTT
ADAMS
SMITH
KING
edited Nov 27 '18 at 9:38
Adrian W
1,97651522
answered Nov 27 '18 at 8:37
Prabodh JenaPrabodh Jena
12
add a comment |
0
0
0
I have used With Clause :
with emp_data as (select EMPNO,DEPTNO from emp)
select ENAME from emp e,emp_data ed
where e.mgr =ed.empno(+) and
(ed.deptno not in (10,30) or e.mgr is null);
ENAME
------
FORD
SCOTT
ADAMS
SMITH
KING
edited Nov 27 '18 at 9:38
Adrian W
1,97651522
answered Nov 27 '18 at 8:37
Prabodh JenaPrabodh Jena
12
I have used With Clause :
with emp_data as (select EMPNO,DEPTNO from emp)
select ENAME from emp e,emp_data ed
where e.mgr =ed.empno(+) and
(ed.deptno not in (10,30) or e.mgr is null);
ENAME
------
FORD
SCOTT
ADAMS
SMITH
KING
edited Nov 27 '18 at 9:38
Adrian W
1,97651522
answered Nov 27 '18 at 8:37
Prabodh JenaPrabodh Jena
12
edited Nov 27 '18 at 9:38
Adrian W
1,97651522
edited Nov 27 '18 at 9:38
Adrian W
1,97651522
edited Nov 27 '18 at 9:38
Adrian W
1,97651522
1,97651522
answered Nov 27 '18 at 8:37
Prabodh JenaPrabodh Jena
12
answered Nov 27 '18 at 8:37
Prabodh JenaPrabodh Jena
12
answered Nov 27 '18 at 8:37
Prabodh JenaPrabodh Jena
12
12
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53488610%2fwrite-a-query-to-show-ename-who-dont-have-reporting-manager-in-deptno-10-or-30%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Hint:
JOINandWHEREorNOT EXISTS.– Gordon Linoff
Nov 26 '18 at 20:35
Are you hinting towards a self-join as there is no other manager table present?
– Alok Padhi
Nov 26 '18 at 20:36