Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009

    Unanswered: Trying to get last records across mutliple tables

    Hi all,

    this is not a question about getting the very last record entered or the latest record across tables.

    Instead, I have a set of tables from which i need to get the latest record for each "ID" value and then link to a user_id in a another table.
    I have tried a few different queries but with no success.

    for instance:

    TABLE 1 (t1): "Details 1" table

    table_id, job_id, summary, user_id, update_time
    1, 20, tex1, 6, 07:15
    2, 21, tex2t, 8, 13:30
    3, 22, text3, 9, 08:21

    TABLE 2 (t2): "Details 2" table

    table_id, job_id, other-details, user_id, update_time
    11, 20, othertext, 6, 08:15
    12, 21, othertext, 8, 11:30
    13, 22, othertext, 4, 08:00

    TABLE 3 (t3): A General Update table

    table_id, job_id, user_id, update_time
    1, 20, 6, 08:00
    2, 21, 8, 14:30
    18, 22, 4, 08:07

    TABLE 4 (t4): User Table

    user_id, username
    4, Peter
    6, Paul
    8, Mary
    9, Sarah

    so, what i would like to do is get the latest record for each Job_ID and link that back to the user_id in the UserTable, displaying details from the first table, as well (summary text)

    For example, I would like to return the following:

    job_id, user_id, username, summary, update_time
    20, 6, Paul, text1, 08:15 (latest userfrom TABLE 2)
    21, 8, Mary, text2, 14:30 (latest user from Table 3)
    22, 9, Sarah, text3, 08:21 (latest user from table 1)

    any help would be appreciated.

    Last edited by DJmysql; 03-14-12 at 23:34. Reason: updated tables

  2. #2
    Join Date
    Sep 2009
    San Sebastian, Spain
    I am not 100% sure what you are looking for here? In particular with the (lastest user from table2, table3 or table1). What rules should be put in to determine which table?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    Follow me on Twitter

  3. #3
    Join Date
    Feb 2009

    thanks for your reply.
    i think i may have been a bit unclear...

    Tables 1, 2 and 3 include records for the same Job_ID's but each contains different information.


    Tables 1,2 and 3 include the update_time for that record and who updated the record (user_id).

    Table 4 lists the user_id's and username

    - I would like to find who made the latest update for each Job_Id record, checking across each table for the latest record

    - I would also like to include in the output some info from Table 1 (summary field)

    i hope that is clearer.

    thanks for any assistance.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts