Solved

help with CFQuery - Joining tables - display current status

Posted on 2007-12-05
25
244 Views
Last Modified: 2013-12-24
hi all,

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">
SELECT *
FROM ops_log_entry
ORDER BY ID DESC
</cfquery>

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">
SELECT current_status
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)
</cfquery>

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 :-(
0
Comment
Question by:bede123
  • 13
  • 11
25 Comments
 
LVL 29

Expert Comment

by:Badotz
ID: 20416263
If the ID numbers grow as the rows are inserted into the table, perhaps MAX(ID) would be a better choice?
0
 
LVL 1

Author Comment

by:bede123
ID: 20417817
yes i cant be sure to get the right result as ther will multipul entries by different users. i think max id might give me the wrong results
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20417961
I think you'll need a combination of max datetime and id, in case two records have the same datetime stamp.  Assuming your second table has a unique id, try something like this.  Just change "TheUniqueID" to the name of your ID column.


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

Open in new window

0
 
LVL 1

Author Comment

by:bede123
ID: 20418634
qucik question, should "TheUniqueID" in your sql above be the ID of the first table that holds the original entry or the ID of the second table that holds the current status?

i dont seem to be able to get it to work. its taking a status from  the second table but its just displaying the same status for row of data, but of course each row will have its own status, they wont all be the same.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20420935
"TheUniqueID" should be the ID of the second table:  new_log_entries

0
 
LVL 1

Author Comment

by:bede123
ID: 20421206
ok tried that didnt work.

in the second table there are two ID columns one called new_entry_id (this is the auto id) the other one is called id_original_entry (this is the one that matches the auto ID of the first table)

i cant get it to work with either of these, it just displays the same status for all records. and whats even more confusing is that the status that being displayed isnt even the MAX ID or MAX DateTime - so whats that all about?

i havent made anymistakes here have i?

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.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
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20421374
No it looks correct.   Let's say this is the sample data in the two tables

ops_log_entry
id      Title
1      item 1
2      item 2
3      item 3

new_log_entries
new_enew_entry_id      id_original_entry      current_status      new_entry_datetime
1      1      started      12/4/2007 11:34:01 AM
2      1      work a      12/6/2007 7:34:01 AM
3      1      work b      12/6/2007 7:34:01 AM
4      3      finished      12/6/2007 9:34:21 AM
5      2      opened      12/5/2007 2:34:27 PM
6      2      closed      12/6/2007 10:34:31 AM

The query would return

ID      current_status
1      work b
2      closed
3      finished

It returns the status "work b" for ID #1 because its the record in the second table with the maximum datetime and ID number.  Are these the results you're looking for?
0
 
LVL 1

Author Comment

by:bede123
ID: 20423076
yes i have read what you havewritten carefully and you are correct. the only difference is the last bit, the query is not pulling "work b" for ID #1

take a look, ive uploaded a working version here:

http://www.alliedcontrol.net/index.cfm?log

add a new entry then click on the +plus sign to add a new status, add a few you'll see how is works. i have made the column red that i am having trouble with.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20423236
Yes, but the query posted would produce "work b" for #1.  So unless I've misunderstood something,  that suggests your query code is different or perhaps your cfoutput code is wrong.    

If you dump the query before doing your cfoutput.  Are all the statuses "Re-opened" or are they different?
<cfdump var="#yourQuery#">

0
 
LVL 1

Author Comment

by:bede123
ID: 20423415
ok this is the result of the cf dump (which i have never done before, very handy thanks)

 CURRENT_STATUS ID
1 Re-opened  16  
2 Waiting on client  18  
3 Closed  19  
4 Remains open  21  
5 Waiting on guard  22  
6 Waiting on guard  23  
7 Closed  25  

so what does this tell us? is the output incorrect?
0
 
LVL 1

Author Comment

by:bede123
ID: 20423434
by the way to output the "current_status" i'm using: {display_log1.current_status}
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20423447
> is the output incorrect?

Yes.   If the query results are correct, and you're using that query to produce the output, then .. yes, the problem must be in your output code.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 52

Expert Comment

by:_agx_
ID: 20423481
> by the way to output the "current_status" i'm using: {display_log1.current_status}

I think that's your problem.  Unless you're looping through the "display_log1" query,  

      #display_log1.current_status#  

only outputs the status in the first record.  Make sense?
0
 
LVL 1

Author Comment

by:bede123
ID: 20423720
ahhhh right, now we're getting somewhere.

so this {display_log1.current_status} is no good. i need loop through the display_log1 query?

to do this i just put cfloop infront of the query dont i? like....

<cfloop query name"display_log ...................

would that work?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20423843
It should, but it depends on your output code.  Also, if you're using nested query loops you may need to specify the row indexes.  But try it first.   You could also try joining display_log1 with your other query.  Either in a db query or using a QoQ

<cfloop ...>
   <cfloop ...>
   </cfloop>
</cfloop>

0
 
LVL 1

Author Comment

by:bede123
ID: 20424306
ok so i wrapped the output in a cfloop like so:

<cfloop query="display_log1>#display_log1.current_status#</cfloop>

what this has done is place all of the results in every row. so do i now need to add a condition to the loop? or something?
0
 
LVL 1

Author Comment

by:bede123
ID: 20424314
you could see whats happening here:

http://www.alliedcontrol.net/index.cfm?log
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20424386
Oh, I see what you're saying.  You have to either JOIN display_log1 to the other query you're using in the output ... or do some kind of conditional loop

<cfloop query="display_log1>
     <cfif display_log1.The_original_ID EQ  theOuterQuery.The_Original_ID>
          then display current_status
     </cfif>
</cfloop>

But again, you may have to use a row index if you're using nested loops.   I can't say more without seeing the output code.

0
 
LVL 1

Author Comment

by:bede123
ID: 20424730
nearly there...

not sure i know how to join the two queries properly so i first off tried the cfif route. tried lots of different combinations of the cfif:

<cfloop query="display_log1">
<cfif display_log1.ID_original_entry EQ display_log1.id_original_entry>#display_log1.current_status#</cfif>
</cfloop>

also not sure about the row indexing you mentioned. played around with startrow"?" endrow"?" but that didnt work, off to bed now, look at it in the morning with fresh eyes! :-)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20424757
Could you post the output code tomorrow?  That might help us "cut to the chase" so to speak ;-)

0
 
LVL 1

Author Comment

by:bede123
ID: 20426362
Good morning,

i thought the best thing would be to post the entire page. i appreciate your help with this.


 

<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#">

Open in new window

0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 20430076
Hi,  I think all you need to do is combine the two queries, then get rid of the inner cfloop.   Assuming no typos in the column names, this should do it.


COMBINE THE TWO QUERIES INTO ONE
 

<cfquery name="display_log" datasource="allied_control_db1">

SELECT  L.site_id_no, L.ops_type, L.site_id_name, L.ops_priority,

        L.log_entry, L.log_entry_date_time, 

	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.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>
 

REMOVE THE CFLOOP AND REPLACE THAT <TD> WITH
 

<td bgcolor="##FF0000">#display_log.current_status#</td>

Open in new window

0
 
LVL 1

Author Comment

by:bede123
ID: 20431922
woooo hooooo its workin! thanks so much for your time and paitence. ive learnt alot. i'm going to read up on joining queries, i think this is something i'll be using a lot of with this site.

thanks again.
0
 
LVL 1

Author Closing Comment

by:bede123
ID: 31413008
thanks agx, you have been extreemly helpful. no doubt i'll be posting for help again in the future. thanks.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20431973
You're welcome!  Yes, read all you can about JOINs.  They're very powerful and you'll almost certainly need them in most applications.  Happy reading :-)
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now