Results 1 to 17 of 17

Thread: Employee and manager name in single query

  1. #1
    Geek_Guest
    Guest

    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

    Question asked by visitor Rajeev


  2. #2
    Contributing Member
    Join Date
    Apr 2006
    Answers
    46

    Re: Employee and manager name in single query

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


  3. #3
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Employee and manager name in single query

    Kindly post your table structure for more details .


  4. #4
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Employee and manager name in single query

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


  5. #5

    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.

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


  6. #6
    Contributing Member
    Join Date
    May 2008
    Answers
    39

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


  7. #7
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Employee and manager name in single query

    What is your table structure?


  8. #8
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Employee and manager name in single query

    You can use union all to get your desired output.


  9. #9
    Contributing Member
    Join Date
    May 2008
    Answers
    39

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


  10. #10
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Employee and manager name in single query

    Quote Originally Posted by karthizen View Post
    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)


  11. #11
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    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


  12. #12
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    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


  13. #13
    Contributing Member
    Join Date
    May 2008
    Answers
    39

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


  14. #14
    Contributing Member
    Join Date
    Apr 2008
    Answers
    31

    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.


  15. #15
    Junior Member
    Join Date
    Apr 2006
    Answers
    2

    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


  16. #16
    Junior Member
    Join Date
    Apr 2006
    Answers
    2

    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


  17. #17
    Junior Member
    Join Date
    Jun 2009
    Answers
    18

    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;


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact