Help on Outer Join needed

From: Marcelo Fontinha <ftvr_at_sprynet.com>
Date: 1997/05/30
Message-ID: <338f1ebe.21597572@news.sprynet.com>#1/1

I need to join the following three tables and obtain the result shown below. The result is a list of all the employee's phones and classes they teach. Phones and classes ARE NOT related directly. I am using Oracle 7.3 and PowerBuilder 5.0.02.

I tried this SQL with two outer joins but a get a cartesian product. See result set below.

SELECT distinct e.emp, p.tel, c.class
  FROM employee e,

       phone p,
       class c

 WHERE e.emp = p.emp (+)
   AND e.emp = c.emp (+)

Is this possible to accomplish at all ?
Will it require the use of views and/or temporary tables and/or stored procedures ?
Am I missing something pretty stupid or just brain dead ?

Note: I was able to create a nested report in PowerBuilder and got the desired result but I don't want to use this approach because of performance issues.

Any comments appreciated.

Marcelo

TABLES AND SAMPLE DATA

---

employee    Phone            class
--------    -----            -----

emp         emp tel          emp class
---         --- --------     --- -------
bob         bob 555-5555     Bob SQL I
ted         bob 666-6666     Bob SQL II
joe         bob 777-7777     Bob DBA I
pam         ted 111-1111     Ted ADM. I
tim         ted 222-2222     Ted ADM. II
            ted 333-3333     Joe SQL I
            joe 444-4444     Joe DBA I
            tim 888-8888     Pam SQL I
            tim 999-9999     Pam SQL II

RESULT WANTED (ignore blank rows)

---

emp tel class
--- -------- -------

Bob 555-5555 SQL I
Bob 666-6666 SQL II
Bob 777-7777 DBA I

Ted 111-1111 ADM. I

Ted 222-2222 ADM. II
Ted 333-3333

Joe 444-4444 SQL I
Joe DBA I

Pam          SQL I
Pam          SQL II

Tim 888-8888
Tim 999-9999

RESULT OBTAINED (ignore blank rows)

---

emp tel class
--- -------- -------

Bob 555-5555 SQL I
Bob 555-5555 SQL II
Bob 555-5555 DBA I
Bob 666-6666 SQL I
Bob 666-6666 SQL II
Bob 666-6666 DBA I
Bob 777-7777 SQL I
Bob 777-7777 SQL II
Bob 777-7777 DBA I

Ted 111-1111 ADM. I
Ted 111-1111 ADM. II
Ted 222-2222 ADM. I
Ted 222-2222 ADM. II

Ted 333-3333 ADM. I
Ted 333-3333 ADM. II

Joe 444-4444 SQL I
Joe 444-4444 DBA I

Pam          SQL I
Pam          SQL II
                          

Tim 888-8888
Tim 999-9999 Received on Fri May 30 1997 - 00:00:00 CDT