Yeah. Because it's not the current row. The current row is whatever row the current query is on.
Change it to qGetPoems.ID and it will work. See, it pulls in ALL the scores that match that specific poem and averages them. Make sense? Also, as a side note, PLEASE do not make your query names like this. Yes, it may be what they taught you in school, but as a person who may be taking over your work in the next phase, I tend to have bad thoughts about people who put the q in queries. :) Also, wouldn't the PoemTitle be the better field to order the query on? Since you are displaying a list, the ID won't matter to people, but if you put it in order that they can see, it will look much nicer. :) Lastly, I won't go into the ID field except to say that when you start doing multiple combined queries, naming it PoemID would make MUCH more sense so you don't have ID,ID,ID,ID,ID. Instead you would have JudgeID, PoemID, RatingID, CustomerID, OrderID or some such. :) Good habbit to get into. On Thu, Jan 13, 2011 at 1:25 PM, Adam Parker <[email protected]> wrote: > > Thanks for the response, Phillip. > > I have combined the ratings into the judges table to only have two tables > instead of three. I am not able to get the average rating, or score, after > doing this. > > <!---Get poems---> > <cfquery name="qGetPoems" datasource="myDSN"> > SELECT id, poemTitle FROM Poems order by id asc > </cfquery> > > <table> > <tr> > <th>ID</th> > <th>Title</th> > <th>Rating</th> > <th>Judge(s)</th> > </tr> > <cfoutput query="qGetPoems"> > <tr> > <td>#id#</td> > <td>#poemTitle#</td> > <!---Get Score---> > <cfquery name="qGetRating" datasource="myDSN"> > SELECT AVG(rating) as TotalScore > FROM Judges > WHERE PoemID = #currentRow.PoemID# > </cfquery> > <td>#qGetRating.TotalScore#</td> > <td>Judge 1</td> > </tr> > </table> > > I'm not certain how to get the specific average score for the proper poem in > each row. I thought the currentRow function would help, but it seems to > error any way I use it. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:5200 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-newbie/unsubscribe.cfm
