Results 1 to 10 of 10

Thread: query problem

  1. #1
    Join Date
    Dec 2004
    Posts
    37

    query problem

    Hi, I need to do a query which will display employees and also their account manager I was wondering what is the best way to display this query so that the account managers name will appear beside the employee name because when i do it they are displayed one on top of another?

    Thanks in advance.
    Last edited by 182; 01-09-2005 at 08:47 PM.

  2. #2
    Join Date
    Mar 2003
    Posts
    468
    you will need to join the table to itself so that both columns can be returned on each row for the result set.

  3. #3
    Join Date
    Dec 2004
    Posts
    37
    I have tried that but it still doesn't seem to work.

    Although I can get both results using to different queries is there anyway which I could join those together instead?

  4. #4
    Join Date
    Dec 2004
    Posts
    502
    If I understand you correctly, you have a table like:

    tbl_EMPLOYEES
    employeename varchar(20) null,
    position varchar(20) null,
    department int


    With data like:

    joe blow_____grunt_____1
    big shot_____manager___1
    john nobody__slave_____1
    big wig______manager___2
    jim slim_____slave_____2
    jack slack___peon______2


    If so, then you can use an SQL statement like:

    select tbl_employees.employeename, tbl_employees.position, tbl_employees.department, managers.employeename as manager
    from tbl_employees
    inner join
    (select employeename, department from tbl_employees where position = 'manager') AS managers
    on tbl_employees.department = managers.department
    where position <> 'manager'

  5. #5
    Join Date
    Mar 2003
    Posts
    468
    post your table definition and queries and we will see if we can get a join going for you if you cant get it working

  6. #6
    Join Date
    Dec 2004
    Posts
    37
    Table Defs:
    employee(employeeNo, name, managementNo )
    management(managementNo,employeeNO)
    Last edited by 182; 01-07-2005 at 01:55 PM.

  7. #7
    Join Date
    Mar 2003
    Posts
    468
    > select * from employee;

    EMPLOYEENO NAME MANAGEMENTNO
    ---------- ---------- ------------
    1 me 2
    2 my manager

    > select * from management;

    MANAGEMENTNO EMPLOYEENO
    ------------ ----------
    2 2

    > select emp.name "employee", man.name "manager"
    2 from employee emp, employee man, management
    3 where emp.managementno = management.managementno
    4 and management.employeeno = man.employeeno;

    employee manager
    ---------- ----------
    me my manager

  8. #8
    Join Date
    Dec 2004
    Posts
    37
    Thats what I had from the start but I seem to be getting this:

    employee
    ----------
    manager
    ----------
    me
    my manager
    Last edited by 182; 01-09-2005 at 08:49 PM.

  9. #9
    Join Date
    Dec 2004
    Posts
    37
    Thanks for the advice.
    Last edited by 182; 01-07-2005 at 03:21 PM.

  10. #10
    Join Date
    Mar 2003
    Posts
    468
    your output looks good to me.
    it has just wrapped.
    if this is sql/plus check your linesize
    you can issue
    set linesize 100
    and issue the query again.


    > create table employee(employeeNo number, name varchar2(10));

    Table created.

    > create table management(managementNo number,employeeNo number);

    Table created.
    > insert into employee values (1,'me');

    1 row created.
    > insert into employee values (2,'my manager');

    1 row created.
    > insert into management values (2,1);

    1 row created.

    > set linesize 132
    > select emp.name, man.name
    from employee emp, employee man, management
    where emp.employeeno = management.employeeno
    and man.employeeno = management.managementno;

    NAME NAME
    ---------- ----------
    me my manager

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •