Home » SQL & PL/SQL » SQL & PL/SQL » Each employee with Manager select (Oracle 9i R2)
Each employee with Manager select [message #342251] Thu, 21 August 2008 15:46 Go to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Hi,
I want to retrieve the data for each employee information with employee manager.
We are storing manager id in other table and employee fname and lname in other table as below.
I am using oracle 9i.
When I write the query i can get mgr_id but i need to know the manager;s first name and last name too.

CREATE TABLE USER
(
USER_ID VARCHAR2(20 BYTE) NOT NULL,
FNAME VARCHAR2(40 BYTE),
LNAME VARCHAR2(40 BYTE),
EMAIL VARCHAR2(80 BYTE),
CONSTRAINT USER_PK PRIMARY KEY (USER_ID),
)

CREATE TABLE EMP
(
USER_ID VARCHAR2(20 BYTE) NOT NULL,
JOB_TITLE VARCHAR2(40 BYTE),
DEPT_CODE VARCHAR2(10 BYTE),
DEPT_DESC VARCHAR2(40 BYTE),
BIRTH_DATE VARCHAR2(35 BYTE),
MGR_ID VARCHAR2(20 BYTE), 
ACTIVE_FLAG NUMBER(2),
CONSTRAINT EMP_PK PRIMARY KEY (USER_ID),
FOREIGN KEY (USER_ID) 
REFERENCES USER (USER_ID),
)


output will be:

USER_ID User_Fname User_Lname User_Email Emp_Job_Title Emp_Mgr_id Emp_Mgr_Fname||Lname Active_Flag
1 James Adam Ja@abc.com DA 5 Scott Tiger 1
2 John ding jd@abc.com AC 5 Scott Tiger 1
3 Vince dingo vd@abc.com mgr 4 Jeff marsh 1
4 Jeff Marsh jm@abc.com vp 10 Willi goodman 1
10 Willi Goodman wg@abc.com CEO 1 



Thanks,
Re: Each employee with Manager select [message #342252 is a reply to message #342251] Thu, 21 August 2008 15:52 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


if you designed to 3rd Normal Form, only 1 table would exist & you would not be facing this challenge

As it stands, a simple join is the solution.
Re: Each employee with Manager select [message #342258 is a reply to message #342252] Thu, 21 August 2008 16:25 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks for your prompt response.
I couldn't understand which guidelines i have violated, could you please point it out exactly so i won't make any same mistake next time.
Really appreciate it.
I tried to join but it gives me only mgr_id not manager's name like employee name.

select user_id,fname,lname,email,job_title,mgr_id,mgr_id
from User , emp
where  User.user_id = emp.user_id


I need here mgr_id with manager's name which is stored in USER table as fnameand lname.

Thanks,
Re: Each employee with Manager select [message #342259 is a reply to message #342251] Thu, 21 August 2008 16:33 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
* Post exact Database (4 decimal places, please) and OS version. By doing so, you'll avoid reactions using features you can't use.


Practice


* Show us what you did (if you tried it yourself), including errors and/or why the result is not what you want.
* Provide DDL (CREATE TABLE, etc.) as appropriate instead of listing the table structure. It'll help people setting up a test set (yes, some people really do that)
* Provide INSERT statements for sample data instead of pasting in or mocking up the results of a SELECT statement.

>I tried to join but it gives me only mgr_id not manager's name like employee name.

you never asked for the manager's name; which is why it was not returned.
How is Oracle supposed to know you want the manager's name returned.?

Re: Each employee with Manager select [message #342260 is a reply to message #342259] Thu, 21 August 2008 16:44 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks so much for proper explaination.
I thought it will be simple known emp query so i willnot need full version and also thought that it will be know emp, dept, mgr table structure.
But I am wrong, i should provide full details.

I also mentioned that "When I write the query i can get mgr_id but i need to know the manager's first name and last name too.
"

Which is associated in User table.

Thanks for your help!
Re: Each employee with Manager select [message #342289 is a reply to message #342260] Thu, 21 August 2008 23:26 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Try Something Like:-

Select 
 (select lname from user where user_id=x.MGR_ID) lname
from
 (user
  emp join) x


Regards,
Rajat
Re: Each employee with Manager select [message #342298 is a reply to message #342251] Thu, 21 August 2008 23:42 Go to previous messageGo to next message
krishnakkk
Messages: 4
Registered: August 2008
Junior Member
select * from emp e1,user u1 where e1.user_id=u1.user_id;
Re: Each employee with Manager select [message #342301 is a reply to message #342251] Thu, 21 August 2008 23:49 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
select count(*) from dual;
is as useful as the 2 previous posts.
Re: Each employee with Manager select [message #342304 is a reply to message #342301] Thu, 21 August 2008 23:54 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Anacedent wrote:
Quote:

select count(*) from dual;
is as useful as the 2 previous posts.



Please clarify What's wrong in my post.If i am missing something then please share so that i can learn from it.

Regards,
Rajat
Previous Topic: Problem with join and finding data between 2 dates
Next Topic: Bulk Insert
Goto Forum:
  


Current Time: Tue Dec 06 06:05:50 CST 2016

Total time taken to generate the page: 0.10634 seconds