sql - Max date problem on joined table -


itemprop = "text">

example for use date: 2011/03/16 as of today's date

table record: record_id, record_status, record_submitted_date

table Record_change_log: Change_log_id, Record_id, will new_record_status, record_change_date, old_record_status

table record data (only a record_id): 

  1, key, 2011-03-14 08: 24: 12.04051-04 2, manual, 2011-03-15 09:56 56: 32.04051-04 3, recorded, 2011-03-16 07:44: 01.04051- 04   
 (must be entered in several record_id and the record_change_date) table record_change_log data:  
  100, 3,, 2011-03-16 09: 54: 01.04051-04, '101, 3, Up Retired, 2011-03-16 09:55: 01.04051-04, 102, 2, entered the manual, 2011-03-15 09: 56: 32.04051-04, '' 103, 3, valid, 2011 -03 -16 10: 00: 01.04051-04 updates, 104, 2, updated, 2011-03-15 10: 01: 51.04051-04, manual 105, 3, back, 2011-03-16 10: 11: 22.04051- 04, Valid 106, 1, Updated, 2011-03-16 11: 11: 11.04051-04, Key 107, 23, booklet, ... 108, 100, test, ...   
 I need a daily return of all records_ID with the final record log. Then 1 record_id will return:  
  106, 1, update, 2011-03-16 11: 11: 11.04051-04, key   
 and 2 record_id Date will not return:  
  104, 2, updated, 2011-03-15 10: 01: 51.04051-04, manual   
 And record_id 3 will come back:  
  105, 3, back, 2011-03-16 10: 11: 22.04051-04, legitimate   
 but In the query I only need record_id and if the new_record_status returned that has changed with today's date, then record_id will not be in 2 results, but would be 1 and 3.  
 Now the rest of the query I only record table requires Rikord_aidi, so do not change back the other records in the log table return:  
  107 , 23, manual, ... 108, 100, test, ...   
 So the desired results are as these changes are the last entries in the log table, as well as in the record table The records are:  
  105, 3, back, 2011-03-16 10: 11: 22.04051- 04, valid 106, 1, update I have tried to join the tables but I can not find this limit. The result in the change log table. And I got out of Group results in some   
 
 
 You can join a correlated sub_query, such as ...  
 select  * enter INNER from the record, turn on record_change_log, record_change_log .change_log_id = (SELECT change_log_id FROM record_change_log WHERE Record_id = record.record_id ORDER by record_change_date DESC LIMIT 1)   
 (I came to you " H only "parts, as I think" just one record from the change log "is the part that you wanted help.)

Comments

Post a Comment