# Databases > SQL Employee and manager name in single query

## Geek_Guest

How can I get both employee name and manager name in single query where employee is also manager and manager has suffix '00' and employee has suffix <> '00'
Ex: 
Employee manager

Emp1 emp5
emp2 emp5
emp3 emp4
emp4 emp4
emp5 emp5

Thanks
Rajeev

*Question asked by visitor Rajeev*

----------


## bhaski

pls send detail data of your table and req. o/p

----------


## debasisdas

Kindly post your table structure for more details .

----------


## krishnaindia2007

select emplyeecode,empployeename,managercode,managername
from xxxx a, xxxx b
where a.managercode = b.employeecode

----------


## sridharrganesan

You can try with this query. Anyhow if you provide the exact table structure, it will be easy to provide the exact query.

*select emp_name, desgn from emp where desgn="Manager" and emp_code like '00%'*

----------


## karthizen

I dont think the above query will work out..

I have the same question....

EMPLOYEE table has both managers and non-managers(employees).. I need to list out them separately in a single query.

I  linked emp table and manager table and got the managers list only...

how to get the other...

i want the result like

Managers

a
b
b
d

Non managers

p
q
r
s

Please help me out....

----------


## krishnaindia2007

What is your table structure?

----------


## krishnaindia2007

You can use union all to get your  desired output.

----------


## karthizen

Sir, i used union all & got it... But i need to get my result in the following format.. Empid name designation 101 karthi manager 102 krishna manager 103 ram non manager ------- like wise.. 

Please find the structure:

Employee table: empid not null number(5) firstname varchar2(15) lastname varchar2(15) age number(3) gender varchar2(2) deptid number(5) 


manager table mgrid not null number(5) empid number(5) deptid number(5) .............

----------


## krishnaindia2007

> sir, i used union all & got it... But i need to get my result in the following format.. Empid name designation 101 karthi manager 102 krishna manager 103 ram non manager ------- like wise.. Please find the structure: employee table: empid not null number(5) firstname varchar2(15) lastname varchar2(15) age number(3) gender varchar2(2) deptid number(5) manager table mgrid not null number(5) empid number(5) deptid number(5) .............


select a.empid,a.firstname,a.lastname,'manager' 
from employee a , manager b 
where a.empid = b.mgrid 
union all 
select empid,firstname,lastname,'non manager' 
from employee 
where empid not in (select mgrid from manager)

----------


## krishnaindia2007

You can do it in the following way also 
select empid,firstname,lastname, 
(case when empid in (select mgrid from manager ) then 'manager' else 'non manager' end) designation 
from employee

----------


## krishnaindia2007

This is another way to get your output using DECODE function

SELECT EMPID,FIRSTNAME,LASTNAME, 
DECODE(  EMPID,(SELECT MGRID FROM  MANAGER ),'MANAGER','NON MANAGER' ) DESIGNATION
FROM EMPLOYEE

----------


## karthizen

I got it this way

select e.empid, Mgrid, decode(Mgrid,NULL,'NONMANAGER','MANAGER') as DESIGNATION 
from Employee e,MANAGER m
where e.EMPID=m.EMPID(+)
order by DESIGNATION;

Thanks a lot krishna !!!!

----------


## nageshkota

Hi,

This Decode function will work in the Oracle only, but if we use the case then it will work for the all.

----------


## uday_son1983

select a.ename,b.ename from emp where emp a, emp b where a.empno=b.mgr;

U can is this query to get the result manager name and emp name in a single query

----------


## uday_son1983

select a.ename,b.ename from emp a, emp b where a.empno=7698 and b.empno=a.mgr;

This Query for selecting the particular Emp name & Manager name for the given emp no

----------


## imtiazali

hi,

   this is simple question,execute this statement on predefined table in oracle 


-->select e.ename employee,m.ename manager from emp e join emp m where 
     e.mgr=m.empno;

----------

