Employee and manager name in single query

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

[b]Question asked by visitor Rajeev[/b]

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

Kindly post your table structure for more details .

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

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

<b>select emp_name, desgn from emp where desgn="Manager" and emp_code like '00%'</b>

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....

What is your table structure?

You can use union all to get your desired output.

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) .............

[quote=karthizen;30707]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) .............[/quote]

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)

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

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

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 !!!!

Hi,

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

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

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

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;