Home » Developer & Programmer » Forms » show manager with employee
show manager with employee [message #569792] Thu, 01 November 2012 11:26 Go to next message
hassan08
Messages: 91
Registered: June 2011
Location: egypt
Member
i have 3 tables
table employee
employee_no
employee_name

table manager
manager_id
manager_name

table department
dept_id
dept_name

how can show on the tree all manager with employee with department
example
manager ali
employee mohamed
department marketing
Re: show manager with employee [message #569879 is a reply to message #569792] Fri, 02 November 2012 12:50 Go to previous messageGo to next message
joy_division
Messages: 4503
Registered: February 2005
Location: East Coast USA
Senior Member
Impossible, as there is NO link defined between these tables.

[edit] ooops, left out the bolded word. completely changed the meaning of the response.

[Updated on: Mon, 05 November 2012 12:38]

Report message to a moderator

Re: show manager with employee [message #569896 is a reply to message #569879] Fri, 02 November 2012 17:43 Go to previous messageGo to next message
msaleem
Messages: 37
Registered: November 2006
Location: Pakistan
Member

Table employee should have columns for manager_id and of course dept_id, otherwise joy is absolutely alright
Re: show manager with employee [message #570116 is a reply to message #569896] Mon, 05 November 2012 14:19 Go to previous messageGo to next message
hassan08
Messages: 91
Registered: June 2011
Location: egypt
Member
tables relations dept_id and manager_id foreign key from tables employee
Re: show manager with employee [message #570149 is a reply to message #570116] Mon, 05 November 2012 23:34 Go to previous messageGo to next message
xpact83
Messages: 218
Registered: October 2008
Location: philippines
Senior Member
select b.manager_name , a.employee_name , c.dept_name
  from employee  a, manager  b  , department  c
 where a.manager_id  = b.manager_id 
  and  a.dept_id = c.dept_id
Re: show manager with employee [message #570175 is a reply to message #570149] Tue, 06 November 2012 05:09 Go to previous messageGo to next message
hassan08
Messages: 91
Registered: June 2011
Location: egypt
Member
thank you for this code but how can show in the tree meaning show manager, employee, department in tree
Re: show manager with employee [message #570221 is a reply to message #569792] Tue, 06 November 2012 21:39 Go to previous message
xpact83
Messages: 218
Registered: October 2008
Location: philippines
Senior Member
Is this what you want?

    select  lpad(' ',level*2,' ')||ename name, dname, level
    from emp a join dept b using (deptno)
    START WITH  mgr is null
    CONNECT BY PRIOR EMPNO = MGR
    union  all
    select  dname, dname,0 from dept
    order by dname , 3
Previous Topic: Absent Employee Report Query
Next Topic: Issue in firing of when-create-record trigger
Goto Forum:
  


Current Time: Thu Aug 21 03:22:46 CDT 2014

Total time taken to generate the page: 0.10032 seconds