help with CFQuery - Joining tables - display current status

SELECT  L.ID, N.current_status
FROM  (  ops_log_entry AS L INNER JOIN new_log_entries AS N 
         ON L.ID = N.id_original_entry 
      )
      INNER JOIN 
      (
	   SELECT  N1.id_original_entry, MAX(N1.TheUniqueID) AS MaxNewLogID
	   FROM    new_log_entries AS N1  INNER JOIN 
              (    SELECT   id_original_entry, MAX(new_entry_datetime) AS MaxDateTime
                   FROM     new_log_entries 
                   GROUP BY id_original_entry
              ) 
              AS D ON (N1.id_original_entry = D.id_original_entry 
                   AND N1.new_entry_datetime =  MaxDateTime)
	   GROUP BY N1.id_original_entry
      ) 
      AS MX ON N.TheUniqueID = MX.MaxNewLogID

Select allOpen in new window

 
<cfquery name="display_log" datasource="allied_control_db1" >
SELECT *
FROM ops_log_entry
ORDER BY ID DESC 
</cfquery>
 
 
<cfquery name="display_log1" datasource="allied_control_db1">
SELECT  L.ID, N.*
FROM  (  ops_log_entry AS L INNER JOIN new_log_entries AS N 
         ON L.ID = N.id_original_entry 
      )
      INNER JOIN 
      (
	   SELECT  N1.id_original_entry, MAX(N1.new_entry_id) AS MaxNewLogID
	   FROM    new_log_entries AS N1  INNER JOIN 
              (    SELECT   id_original_entry, MAX(new_entry_datetime) AS MaxDateTime
                   FROM     new_log_entries 
                   GROUP BY id_original_entry
              ) 
              AS D ON (N1.id_original_entry = D.id_original_entry 
                   AND N1.new_entry_datetime =  MaxDateTime)
	   GROUP BY N1.id_original_entry
      ) 
      AS MX ON N.new_entry_id = MX.MaxNewLogID
</cfquery>
 
 
 
<cfform action="ops/insert_log_entry.cfm" method="post" name="ops_log_form">
<table width="0%" border="0" cellspacing="0" cellpadding="2">
<tr>
 
<td rowspan="2" align="left" valign="top">
<label for="site_id_no">Site/ID</label>
<cfinput type="text" name="site_id_no" id="site_id_no" tabindex="1">
 
<label for="ops_type">Type</label>
<cfselect
tabindex="3" 
name="ops_type"
id="ops_type"
required = "Yes"  
message = "message text"   
onError = "error text" >
<option selected="selected" value="not selected">not selected</option>
<option value="Phoned in">Phoned in</option>
<option value="Phoned out">Phoned out</option>
<option value="Task">Task</option>
<option value="Log entry">Log entry</option>
</cfselect>
</td>
 
<td rowspan="2" align="left" valign="top">
<label for="site_id_name">Site or emp name</label>
<cfinput type="text" name="site_id_name" id="site_id_name" tabindex="2">
 
<label for="ops_priority">Priority</label>
<cfselect 
tabindex="4"
name="ops_priority"
required = "Yes"  
message = "message text"   
onError = "error text" >
<option selected="selected" value="not selected">not selected</option>
<option value="HIGH">HIGH</option>
<option value="Medium">Medium</option>
<option value="low">low</option>
</cfselect>
</td>
 
<td rowspan="2">
<label for="log_entry">Log entry or task</label>
<cftextarea name="log_entry" id="log_entry"  tabindex="5" wrap="virtual"></cftextarea>
</td>
 
<td align="left" valign="top">
<cfinput type="hidden" name="log_entry_date_time" value="NOW())">
<cfinput type="hidden" name="ops_status" value="open">
</td>
 
 
<td align="left" valign="top">
<cfinput name="Submit" type="submit" id="submit_button" value="S" alt="Save">
</td>
    
</table>
 
 
 
 
 
 
 
 
 
 
 
 
 
<table width="800" border="1" align="left" cellpadding="2" cellspacing="0">
  <tr>
    <td>Site/ID No.</td>
    <td>Type</td>
    <td>Name</td>
    <td>Priority</td>
    <td>Log/task</td>
    <td>Entry/task created</td>
    <td bgcolor="#FF0000">Status (each entry should have its own status)</td>
    <td>Options</td>
  </tr>
  <cfoutput query="display_log">
    <tr>
      <td>#display_log.site_id_no#</td>
      <td>#display_log.ops_type#</td>
      <td>#display_log.site_id_name#</td>
      <td>#display_log.ops_priority#</td>
      <td>#display_log.log_entry#</td>
      <td>#DateFormat(display_log.log_entry_date_time)# @ #TimeFormat(display_log.log_entry_date_time)#</td>
      <td bgcolor="##FF0000">
      
<cfloop query="display_log1">
<cfif display_log1.ID_original_entry EQ display_log1.id_original_entry>#display_log1.current_status#</cfif>
</cfloop>
</td>
      <td><a href="../ops/edit_log.cfm?id_log=#display_log.ID#"><img src="../images/gifs/plus.gif" alt="Edit/Add" width="15" height="15" border="0" /></a></td>
    </tr>
  </cfoutput>
</table>
</cfform>
 
<cfdump var="#display_log1#">

Select allOpen in new window