CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Andromeda
Andromeda

Reputation: 12897

Oracle Sql query to get manager to employee relation

I have a table structure like this in which employee to manager relation is defined 

EMP_ID    SUP_ID     START_DATE    END_DATE
emp1      sup1       01-JAN-2012   30-JAN-2012
emp1      sup2       01-FEB-2012   28-FEB-2012
emp2      sup1       01-JAN-2012   28-FEB-2012

I need a query to get all the employees under a manager as of the sysdate.

ie if im executing it on jan with mgr id sup1 it should return emp1 and emp2
if it is on feb it should return emp2 only.

I tried writing the query using joins but its not working and im confused on where to put what conditions.

Upvotes: 1

Views: 886

Answers (2)

Sushant Butta
Sushant Butta

Reputation: 530

select * from employees where sup_id = 'sup1' and start_date = trunc(sysdate);

Upvotes: 0

John Doyle
John Doyle

Reputation: 7793

The below should surely work?

select * from employees
where sysdate between start_date and end_date
and sup_id = 'sup1';

Upvotes: 1

Related Questions