Home » SQL & PL/SQL » SQL & PL/SQL » Empolyees doesn't have any reportess (oracle 11 g )
icon2.gif  Empolyees doesn't have any reportess [message #651937] Fri, 27 May 2016 22:49 Go to next message
saipradyumn
Messages: 404
Registered: October 2011
Location: Hyderabad
Senior Member
Hi all,

Please help to find the out the employees who doesn't have any reporting employees to him .
Re: Empolyees doesn't have any reportess [message #651938 is a reply to message #651937] Fri, 27 May 2016 22:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
saipradyumn wrote on Fri, 27 May 2016 20:49
Hi all,

Please help to find the out the employees who doesn't have any reporting employees to him .



Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

How do YOU write SQL when you do not know table name or column name?
Re: Empolyees doesn't have any reportess [message #651939 is a reply to message #651938] Fri, 27 May 2016 22:56 Go to previous messageGo to next message
saipradyumn
Messages: 404
Registered: October 2011
Location: Hyderabad
Senior Member

We can use that oracle pre defined emp table
Re: Empolyees doesn't have any reportess [message #651940 is a reply to message #651939] Fri, 27 May 2016 23:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
so what have YOU learned since joining this forum in 2011?

WHERE COUNT(*) =0
Re: Empolyees doesn't have any reportess [message #651941 is a reply to message #651940] Fri, 27 May 2016 23:05 Go to previous messageGo to next message
saipradyumn
Messages: 404
Registered: October 2011
Location: Hyderabad
Senior Member
Hi I have these two ways

select ename
  from emp
minus
select ename from emp where empno in (select mgr from emp);

select *
  from emp ee
 where not exists (select 1 from emp e where ee.empno = e.mgr) order by ename 

But still my interviewer  is asked for another alternate 

[Updated on: Fri, 27 May 2016 23:05]

Report message to a moderator

Re: Empolyees doesn't have any reportess [message #651942 is a reply to message #651941] Fri, 27 May 2016 23:26 Go to previous messageGo to next message
garan
Messages: 27
Registered: January 2016
Junior Member
Hi

I don't recall at this point of time I feel there are two other ways you should be able to achieve

1. Explore self join
2. Explore connect by clause and level option

garan
Re: Empolyees doesn't have any reportess [message #651947 is a reply to message #651941] Sat, 28 May 2016 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can simplify your first query to:
SELECT ename FROM emp 
WHERE empno IN (SELECT empno FROM emp MINUS SELECT mgr FROM emp)


About your second query:
SELECT ename FROM emp e
WHERE NOT EXISTS (SELECT NULL FROM emp m WHERE e.empno = m.mgr)

when there is no NULL, NOT EXISTS can be changed to NOT IN (note the use of NVL as MGR column can be NULL, remove it to see what happens):
SELECT ename FROM emp WHERE empno NOT IN (SELECT NVL(mgr,0) FROM emp)


You can use an OUTER JOIN:
SELECT e.ename 
FrOM emp e LEFT OUTER JOIN emp m ON e.empno = m.mgr
WHERE m.mgr IS NULL

Re: Empolyees doesn't have any reportess [message #651948 is a reply to message #651942] Sat, 28 May 2016 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
garan wrote on Sat, 28 May 2016 06:26
...2. Explore connect by clause and level option...


How? Can you show us?

Re: Empolyees doesn't have any reportess [message #651962 is a reply to message #651948] Sat, 28 May 2016 08:37 Go to previous messageGo to next message
garan
Messages: 27
Registered: January 2016
Junior Member
Hi

I think level might not be required the below will get the employee_id who is not a manager(No reportees) and he might be reporting to someone

connect hr/hr( Tried on oracle supplied demo table employees)

select employee_id,manager_id from Employees
where CONNECT_BY_ISLEAF = 1
start with manager_id is null
connect by prior employee_id = manager_id

garan
Re: Empolyees doesn't have any reportess [message #651964 is a reply to message #651962] Sat, 28 May 2016 08:53 Go to previous message
Michel Cadot
Messages: 67920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Oh yes, a complex way to come to it. Smile

With EMP table used above, it is:
SELECT ename FROM emp
WHERE connect_by_isleaf = 1
CONNECT BY prior empno = mgr
START WITH mgr IS NULL

Previous Topic: Last Record
Next Topic: Reverse row to column and column to row
Goto Forum:
  


Current Time: Tue Jul 27 21:01:52 CDT 2021