Home » SQL & PL/SQL » SQL & PL/SQL » Help with query
Help with query [message #287447] Wed, 12 December 2007 04:45 Go to next message
aboodlardy
Messages: 80
Registered: November 2006
Location: India
Member
Hi experts,

I have two tables
1.emp
2.sup (supervisor)


SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------

 PERSON_ID                                 NOT NULL VARCHAR2(23)
 FIRST_NAME                                         VARCHAR2(23)
 EMAIL                                              VARCHAR2(20)

SQL> desc sup
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------

 PERSON_ID                                          VARCHAR2(23)
 SUP_ID                                    NOT NULL VARCHAR2(23)

SQL>

the relation between these two is sup have person_id as foreign in emp. Person_id is the primary key in emp.
and these two have data as follows
SQL> select * from emp;

PERSON_ID               FIRST_NAME              EMAIL
----------------------- ----------------------- -----------------
1                       a                       a@aecl
2                       b                       b@aecl
3                       c                       c@aecl
4                       d                       d@aecl


SQL> select * from sup;

PERSON_ID               SUP_ID
----------------------- --------------------
1                       2
3                       4


I want a query for this result.

First_name, Supervisoremail, person_id, supervisor_id, empemail, supervisor_name.

Please help

[Updated on: Wed, 12 December 2007 04:46]

Report message to a moderator

Re: Help with query [message #287450 is a reply to message #287447] Wed, 12 December 2007 04:54 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
It is a simple join you're looking for:
- use emp twice (one time for the employee details and one time for the supervisor details) and the sup table once.
- join emp.person_id on sup.person_id
- join emp2.person_id on sup.sup_id

This should get you started.

MHE
Re: Help with query [message #288132 is a reply to message #287450] Sat, 15 December 2007 00:19 Go to previous messageGo to next message
aboodlardy
Messages: 80
Registered: November 2006
Location: India
Member
Thanks a lot

I get it.


Thanks once again.
Re: Help with query [message #288134 is a reply to message #287447] Sat, 15 December 2007 00:22 Go to previous message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Congratulations!
Previous Topic: definition of view
Next Topic: sql case tool
Goto Forum:
  


Current Time: Tue Dec 06 06:29:17 CST 2016

Total time taken to generate the page: 0.07667 seconds