• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 610
  • Last Modified:

Oracle SQL - one to many join, use of max

Hi,

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?

Thanks!
0
yelbow
Asked:
yelbow
1 Solution
 
SharathData EngineerCommented:
try this.
SELECT t1.rec_key, 
       t1.table1_data1, 
       t1.table1_data2, 
       t3.who_rec_key, 
       t3.NAME 
  FROM main_table AS t1 
       JOIN (SELECT rec_key, 
                    who_rec_key, 
                    ROW_NUMBER() 
                      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

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I have written an article about this kind of scenarios:
http://www.experts-exchange.com/A_3203.html
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
from
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  
0
 
sunil_rangineniCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now