Who can solve this puzzle? (Complex join with sub-queries)

From: CC <cessna_at_poky.srv.net>
Date: 1997/12/12
Message-ID: <34921ED5.2CA@poky.srv.net>#1/1

I've tried to provide a simplified example of my problem. The following 3 tables have a primary key of "PART, VERSION". The tables are related on "PART". There can be multiple rows for the same PART in each
table. The version is incremented for each time there is a revision for a
PART. The version is not necessarily the same for the same PART in each table. I
want to outer join the highest version for each part.

TABLE_1 will contain a row for all distinct PART values in the 3 tables. TABLE_2 and TABLE_3 may not have some PART rows. I actually have 5 tables
of this type in my actual database structure plus some additional supporting
tables, but we'll use these 3 for simplicity.

KEY
DATA

TYPE          TABLE_1           TABLE_2           TABLE_3
====          =======           =======           =======
VARCHAR2(9)   PART--------------PART--------------PART
NUMBER(3)     VERSION           VERSION           VERSION
              T1_COLUMN_A       T2_COLUMN_A       T3_COLUMN_A
              :                 :                 :
              T1_COLUMN_Z       :                 :
                                T2_COLUMN_Z       :
                                                  T3_COLUMN_Z

Key Example data

    TABLE_1                 TABLE_2                 TABLE_3

PART       VERSION      PART       VERSION      PART       VERSION


=========  =======      =========  =======      =========  =======

111111111  3----------->111111111  2----------->111111111  4
111111111  2            111111111  1            111111111  3
111111111  1       |--->222222222  3------|     111111111  2
222222222  2-------|    222222222  2      |     111111111  1
222222222  1            222222222  1      |---->222222222  1
333333333  1----------------------------------->333333333  2
444444444  1----------->444444444  1            333333333  1

What should be retrieved:

    TABLE_1                 TABLE_2                 TABLE_3

PART       VERSION      PART       VERSION      PART       VERSION


=========  =======      =========  =======      =========  =======

111111111  3            111111111  2            111111111  4
222222222  2            222222222  3            222222222  1
333333333  1            NULL                    333333333  2
444444444  1            444444444  1            NULL

Example SELECT (WHERE Clause is the problem). I expect that subqueries will be
required to find the MAX(version) for each table, but I don't know how to
outer join a subquery. I tried creating views for each table which has the
highest version for each part, and then joining using the views, but it was much
too slow. The tables all have indexes on (PART, VERSION).

SELECT table_1.t1_column_a,
       table_2.t2_column_a,
       table_3.t3_column_a
FROM table_1,
     table_2,
     table_3
WHERE
     ???

I've considered creating a "master table" which has the highest version for each
table. I'd probably use a database trigger to maintain it. I would rather
not do this unless absolutely necessary. BTW, I'm using Oracle v.7.1.5

I hope this makes sense. Thanks for the help. Chris

Please e-mail reply to:
cessna_at_poky.srv.net Received on Fri Dec 12 1997 - 00:00:00 CST