Help needed in solving a query

Question

0.00/5 (No votes)

See more:

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
 1254 TANS       MANAGER         5555 09-OCT-91       5000       5500         30
 123 VIDYA
 7369 SMITH      CLERK           7902 17-DEC-80        800                    20
 7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
 7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
 7566 JONES      MANAGER         7839 02-APR-81       2975                    20
 7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
 7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
 7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
 7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
 839 KING       PRESIDENT            17-NOV-81       5000                    10
 7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
 7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
 7900 JAMES      CLERK           7698 03-DEC-81        950                    30
 7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
 7934 MILLER     CLERK           7782 23-JAN-82       1300                   10

in this table i wants to
Display all the employees who is working as manager for only one employee.

What I have tried:

SQL

select *from emp where empno in (select mgr from emp where job = 'MANAGER')

i tired this sql query but i am not sure weather it is correct or not. please help me to correct.

Posted 19-Feb-18 19:36pm

FireMonkey92

Updated 19-Feb-18 19:41pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2018-02-19T20:03:00

Start by using GROUP BY to get you the number of people who are managed by the same person:

SQL

SELECT Mgr, COUNT(mgr) As [Count] FROM emp
GROUP BY Mgr
HAVING COUNT(Mgr) = 1

You can then use JOIN to get the manager details:

SQL

SELECT * FROM emp e
JOIN (SELECT mgr, COUNT(mgr) As [Count] FROM emp
      GROUP BY Mgr
      HAVING COUNT(Mgr) = 1) c
ON e.EmpNo = c.Mgr