Oracle SQL - one to many join, use of max

Posted on 2011-03-02
Last Modified: 2012-05-11

In an Oracle 11 databaseI have three tables.  The main_table has a structure as follows:

      rec_key      CHAR(20)
      tab1e1_data1 CHAR(10)
      table1_data2 CHAR(10)

and contains the following data:

      01234567890000100001      first_line_1      first_line_2
      01234567890000200001      first_line_1      first_line_2

The second_table, which contains details of record edits,  has the following structure:

      rec_key          CHAR(15)
      date             CHAR(8)
      hour             CHAR(4)
      who_rec_key  CHAR(10)

and contains data as follows:

      012345678900001            20110302      0830      0000000001
      012345678900001            20110301      1446      0000000002
      012345678900002            20110228      1030      0000000001
      012345678900002            20110302      1530      0000000002

The join between the main_table and the second_table is:

      substr(main_table.rec_key,1,15) = second_table.rec_key
My third_table, which contains more info about the "who", has the following structure:

      who_rec_key  CHAR(10)
      name         CHAR(25)
and contains data as follows:

      0000000001            DOE,JOHN
      0000000002            SMITH,JOHN
The join between the second_table and the third_table is:

      second_table.who_rec_key = third_table.who_rec_key
Using the data on each of these -  I want to be able to retrieve each line from the main_table, and the ID and name of the person who was last to edit this record.  Given the data above, I want the query to result in:

      01234567890000100001      first_line_1      first_line_2      0000000001      DOE,JOHN
      01234567890000200001      first_line_1      first_line_2      0000000002      SMITH,JOHN
How do I achieve this with the required joins and needing to use a max statement based on the contents of the two field (date, hour) that give me the "timestamp" of the record edits?

Question by:yelbow
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 41

Accepted Solution

Sharath earned 500 total points
ID: 35015121
try this.
SELECT t1.rec_key, 
  FROM main_table AS t1 
       JOIN (SELECT rec_key, 
                      OVER(PARTITION BY rec_key ORDER BY DATE DESC) AS rn 
               FROM second_table) AS t2 
         ON SUBSTR(t1.rec_key,1,15) = t2.rec_key 
       JOIN third_table AS t3 
         ON t2.who_rec_key = t3.who_rec_key 
 WHERE t2.rn = 1

Open in new window

LVL 28

Expert Comment

by:Naveen Kumar
ID: 35015156
there are many ways to do this.. just one way is here.

try this :

select (
select a.rec_key  from first_table a, second_table b, third_table c
where  substr(a.rec_key,1,15) = b.rec_key
and b.who_rec_key = c.who_rec_key and rownum = 1) rec_key,
(select a.table1_data1  from first_table a, second_table b, third_table c
where  substr(a.rec_key,1,15) = b.rec_key
and b.who_rec_key = c.who_rec_key and rownum = 1) table1_data1,
(select a.table1_data2  from first_table a, second_table b, third_table c
where  substr(a.rec_key,1,15) = b.rec_key
and b.who_rec_key = c.who_rec_key and rownum = 1) table1_data2,    
from third_table c
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35015169
I have written an article about this kind of scenarios:
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35015174
another method and better than the earlier is below.. just test it out :

select a.rec_key, a.table1_data1, a.table1_data2, c.who_rec_key,
first_table a,
select *
from (
select  row_number() over ( partition by rec_key order by date1 desc) rr, s.rec_key, s.who_rec_key
from second_table s )
where rr = 1 ) b,
third_table c
where substr(a.rec_key,1,15) = b.rec_key
and b.who_rec_key = c.who_rec_key  

Expert Comment

ID: 35015810
Try the below query:
select * from (
select rec_key, table1_data1, table1_data2, who_rec_key, name, 
rank() over (partition by substr(main_table.rec_key,1,15) order by to_date(||' '||second_table.hour, 'YYYYMMDD HH:MI AM') Rnk
from main_table, second_table, third_table
where substr(main_table.rec_key,1,15) = second_table.rec_key and
      second_table.who_rec_key = third_table.who_rec_key
where rnk = 1

Open in new window


Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

689 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question