Go Premium for a chance to win a PS4. Enter to Win


Oracle SQL - one to many join, use of max

Posted on 2011-03-02
Medium Priority
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
LVL 41

Accepted Solution

Sharath earned 2000 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,    
c.who_rec_key, c.name
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, c.name
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.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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

916 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