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]
Re: Employee and manager name in single query
pls send detail data of your table and req. o/p
Re: Employee and manager name in single query
Kindly post your table structure for more details .
Re: Employee and manager name in single query
select emplyeecode,empployeename,managercode,managername
from xxxx a, xxxx b
where a.managercode = b.employeecode
Re: Employee and manager name in single query
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>
Re: Employee and manager name in single query
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....
Re: Employee and manager name in single query
What is your table structure?
Re: Employee and manager name in single query
You can use union all to get your desired output.
Re: Employee and manager name in single query
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) .............
Re: Employee and manager name in single query
[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)
Re: Employee and manager name in single query
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
Re: Employee and manager name in single query
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
Re: Employee and manager name in single query
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 !!!!
Re: Employee and manager name in single query
Hi,
This Decode function will work in the Oracle only, but if we use the case then it will work for the all.
Re: Employee and manager name in single query
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
Re: Employee and manager name in 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
Re: Employee and manager name in single query
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;