-
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.
-
you will need to join the table to itself so that both columns can be returned on each row for the result set.
-
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?
-
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'
-
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
-
Table Defs:
employee(employeeNo, name, managementNo )
management(managementNo,employeeNO)
Last edited by 182; 01-07-2005 at 01:55 PM.
-
> 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
-
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.
-
Last edited by 182; 01-07-2005 at 03:21 PM.
-
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
-
Forum Rules
|
|