Home » RDBMS Server » Server Administration » plsql
plsql [message #370058] Thu, 07 December 2000 23:15 Go to next message
sujathaThogachedu
Messages: 3
Registered: December 2000
Junior Member
I have a simple Employee table that contains three fields: Emp ID, Last Name and First Name. The table’s primary key is Emp ID
Sample Employee Table:
EMP_ID LAST_NAME FIRST_NAME
1 Smith Janet
2 Jones Jim
3 Johnson Mike
4 Carlos Frank
5 Brown Jerry

Also, I have a simple Family_Member table that contains: Member ID, Emp ID, Last Name, First Name, Relationship. The table’s primary key is Member ID. This table contains the name of an employee’s family members, if any. If the employee has no family members, there is no entry in this table
Sample Family Member Table:
MEMBER_ID EMP_ID LAST_NAME FIRST_NAME RELATIONSHIP
1 1 Smith Mike Spouse
2 1 Smith Jennifer Daughter
3 4 Carlos Maria Spouse
4 4 Carlos Michael Son
5 4 Carlos Sonya Daughter
6 5 Griggs Jane Spouse
7 5 Griggs Mary Daughter

How to write a single select statement to retrieve all employees and their spouses, if any. Be sure to include employees who do not have any family members. (See sample select output below).
Emp_ID Emp Last Name Emp First Name Spouse Last Name Spouse First Name
1 Smith Janet Smith Mike
2 Jones Jim
3 Johnson Mike
4 Carlos Frank Carlos Maria
5 Brown Jerry Griggs (or Brown) Jane
Re: plsql [message #370060 is a reply to message #370058] Fri, 08 December 2000 00:20 Go to previous messageGo to next message
Balasubramanian.C
Messages: 5
Registered: November 2000
Junior Member
use an join condition like
a.empid=b.empid
where a is an alias name for table a and b is alias name for table b
Re: plsql [message #370077 is a reply to message #370058] Mon, 11 December 2000 15:03 Go to previous message
Shanthi Ramayanapu
Messages: 22
Registered: October 2000
Junior Member
Well, here is the answer. Use outer join. Someone suggested a selfjoin that will give you the result only if a spouse exits. You need to use outer join.
I could send out the code too, but since someothers suggested that this might be a school project, this hint should help you resolving it. Let me know if you couln't.

Shanthi
Previous Topic: Re: What's wrong with the insert statement in a trigger?
Next Topic: user question
Goto Forum:
  


Current Time: Sun Dec 22 21:08:47 CST 2024