help with CFQuery - Joining tables - display current status
Posted on 2007-12-05
simple access db with two tables, one contains the original log entry the other table gets updated as to the status of the log entry, simple.
the first query simply outputs all original log entries:
<cfquery name="display_log" datasource="allied_control_db1">
ORDER BY ID DESC
when inserting new entires into the second table i also pass the ID of the original log entry as well so that i can match up which new entries are related to which original entires - still with me?
this next query is supposed to join the two tables, match up the ID's and take the info from the "current_status" column in second table WHERE MAX DATE/TIME.
the reason for the WHERE is that there may have been loads of entries all linked to the same ID but its the latest one that will hold the most up to date "current_status" - still with me now?
<cfquery name="display_log1" datasource="allied_control_db1">
FROM ops_log_entry AS L INNER JOIN new_log_entries AS N
ON ( L.ID = N.id_original_entry )
WHERE N.new_entry_datetime = (SELECT MAX(N1.new_entry_datetime)
FROM new_log_entries AS N1
WHERE N1.id_original_entry = N.id_original_entry)
the "current_status" should then be displayed in the same table that displays data from the first table (the original entry)
the end result is that you can view a list of all original entires but with their current status.
i'm pretty sure its the MAX DATE TIME thing thats not working it just seems to take any "current_status" and displays the same status for all entries.
could somone please have a look at my code and give me any suggestions, thanks. sorry about any typos, imy very tired and off to bed now :-(