| plsql [message #370058] |
Thu, 07 December 2000 23:15  |
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 #370077 is a reply to message #370058] |
Mon, 11 December 2000 15:03  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
