var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Trying to get last records across mutliple tables
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.
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
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-15-12 at 00:34.
Reason: updated tables
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?
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.