Re: Outer join on multiple tables?

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/03/30
Message-ID: <351ff2ff.35431639@www.sigov.si>#1/1

On Mon, 30 Mar 1998 13:13:17 +0100, Ilidio Oliveira <"ioliv"@inesca.pt(please_remove)> wrote:

>Hi,
>
>I believe outer joins with more that two tables aren't possible, but I
>could use that kind of functionality...
>Here's the situation: suppose I have four tables A, B, C, D. All four
>have exactly the same primary key (no, it wasn't me who design the
>database!), having data for the same entity. Table A always has all
>instances of that entity, but tables B, C and D may have or not the
>corresponding rows.
>I need to run a query which would retrieve *all* rows from
>the table A, as well as the rows from B, C, and D tables where a join
>can be established, this is, an outer join on B, C and D. Can this be
>achieved within the same query? Which approach would you advise?
>
>(I'm using Oracle 7.3)

It is not true that outer joins with more that two tables aren't possible. The only restriction is that "single table can only be the NULL–generated table for one other table". In your case, tables B, C and D will each be NULL-generated table for only one table (table A). So the outer join for your four tables is quite simple - look at the following example:

SQL> SELECT * FROM A;         A

---

        2
        3
        4
        1

SQL> SELECT * FROM B;         A

---

        2

SQL> SELECT * FROM C;         A

---

        3

SQL> SELECT * FROM D;         A

---

        4

SQL> SELECT A.A T1, B.A T2, C.A T3, D.A T4 FROM A, B, C, D

  2  WHERE A.A = B.A(+)
  3    AND A.A = C.A(+)
  4    AND A.A = D.A(+);

       T1        T2        T3        T4
--------- --------- --------- ---------
        1
        2         2
        3                   3
        4                             4

>
>Thanks in advance.
>
>I.O.

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Mon Mar 30 1998 - 00:00:00 CST