File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Lates Timestamp record from 3 tables. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Lates Timestamp record from 3 tables." Watch "Lates Timestamp record from 3 tables." New topic
Author

Lates Timestamp record from 3 tables.

Shaan Shar
Ranch Hand

Joined: Dec 27, 2005
Posts: 1249

Hi ranchers I have one question to ask Let me describe the situation:

There are 3 tables which I need to joint on a single field CALLED AS ID

Now in each table there is a field called as Updt_Timestamp.

Now the problem statement is

Select the record for that particular ID (Which is given ofcourse) from the 3 tables but finally there should be a single record with the latest Updt_Timestamp among those 3 records.



Now I know one method which is, first take all the records in three cursur and then compare there timestamps and then create a flag which will indicate us that from which cursor we are finally taking the record.......

Isn't there any appropriate method.

Because in this flow we are using 3 cursors which are not finally usable only one of them is going to be used.


Early replies are appreciated.

Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Well Your question is not clear to me , could you rephrase it. I can interpret it in two way
  • You want to select from 3 table but timestap should be lates among 3
  • You want to select a data which has latest timestamp


  • both point have different meaning,and I am also not clear about your joins.
    if you are planning for kinda inner join then there would be on cursor etc

    Shailesh


    Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
    Shaan Shar
    Ranch Hand

    Joined: Dec 27, 2005
    Posts: 1249

    Thanks Shailesh for the early reply,

    I want to select the record among these 3 tables which is having Latest Update_Timestamp...







    Let suppose there are three tables a,b,c in

    a ======== 20 Columns
    b ======== 30 Columns
    c ======== 40 Columns

    If a has the record with the latest timestamp then we should retrieve record of a which has 20 columns......

    I hope I cleraed all confusion but still if anyone have any concern then revert back.
    stu derby
    Ranch Hand

    Joined: Dec 15, 2005
    Posts: 333
    You should be able to do this in 2 queries; one to get the timestamps from A, B, or C, and one to get the A, B or C record you need.

    You then compare the 3 different values in Java and then go execute a select against the right table for whatever columns you need.
    Shaan Shar
    Ranch Hand

    Joined: Dec 27, 2005
    Posts: 1249

    Thanks Stu,

    But that I know already but as you can see that there is a multiple hit on Database which is not good for performance tuning.....

    The method, earlier I explained is the other way and also prevent multiple hitting the Database I need some more better Query.



    Shaan Shar
    Ranch Hand

    Joined: Dec 27, 2005
    Posts: 1249

    SELECT a.Update_Timestamp as a_timestamp, b.Update_Timestamp as b_timestamp, c.Update_Timestamp as c_timestampFROM a,b,cWHERE a.ID = ? AND b.ID = a.id AND c.id = b.id



    Even by this query you are selecting the data only on the basis of ID, which is not really I need as I told you that I also need the record from the table with the latest Update_Timestamp field.

    I show what I am using right now...




    Now I am going to check that what is the value of flag.....
    for an example if it is ORC .. then I will fetch the record from Ora_received_Carrier table.....


    Another method I just invented is more complex and very big also I am also showing this method to ranchers.......

    Just confirm me that is there any other method which is more tuned then this one...
    Hope




    now I can make things more clear........

    Waiting for replies....
    Thankx in advance for reply....

    Shaan Shar
    Ranch Hand

    Joined: Dec 27, 2005
    Posts: 1249

    What happen no updates till now, I am waiting still....


    Is there any other method .........


    Jeanne Boyarsky
    author & internet detective
    Marshal

    Joined: May 26, 2003
    Posts: 31070
        
    232

    Originally posted by Ankur Sharma:
    Thanks Stu,

    But that I know already but as you can see that there is a multiple hit on Database which is not good for performance tuning.....

    The method, earlier I explained is the other way and also prevent multiple hitting the Database I need some more better Query.




    I agree with Stu as I think the best approach is to use two queries. "multiple hit on Database which is not good for performance tuning". This isn't always true. I've seen really complex queries that took longer because the database had to do so much extra work to avoid doing a separate query.

    If you are concerned about network traffic, you can do the two queries in a stored procedure.


    [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
    Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
    Shaan Shar
    Ranch Hand

    Joined: Dec 27, 2005
    Posts: 1249

    Jeanne Boyarsky,

    So what do you think about my methodology what I have stated there in my earlier threads. If I write this whole code in Stored Procedures. Then which will be the best option for this sort of operations..

    Jeanne Boyarsky
    author & internet detective
    Marshal

    Joined: May 26, 2003
    Posts: 31070
        
    232

    Ankur,
    Can you provide a link to the previous thread? You have over 200 posts, which is too many to look though to find a specific one.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Lates Timestamp record from 3 tables.