Thanks Ian... i think i had fried my brain during the time it took me to create that query from a few days ago until now... :)
It works now! Stu On 29 Sep 2005, at 12:42, Ian Tait wrote: > You need to use something like ORDER BY authorID > if you're grouping on it. > > Ian > > >> -----Original Message----- >> From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED] >> Sent: 29 September 2005 12:37 >> To: CF-Talk >> Subject: Re: INNER JOIN >> >> Hi, >> >> I've simplified it a bit: >> >> <cfquery name="GetAuthors" datasource="user020"> >> SELECT >> tbl_020authorDetails.authorID, >> tbl_020authorDetails.authorFirstName, >> tbl_020authorDetails.authorSurname, >> tbl_020authorDetails.authorPhotograph, >> tbl_020authorDetails.authorAboutMe, >> tbl_020articleDetails.articleID, >> tbl_020articleDetails.articleTitle >> FROM tbl_020articleDetails >> INNER JOIN tbl_020authorDetails ON >> tbl_020articleDetails.authorID = tbl_020authorDetails.authorID >> WHERE tbl_020authorDetails.authorPhotograph <> '' AND >> tbl_020articleDetails.articleSubTypeID <> 55 </cfquery> >> >> <cfoutput query="GetAuthors" group="authorID"> >> <p><strong>#authorFirstName#</strong></p> >> >> <p>my articles:</p> >> <cfoutput>#articleTitle#<br /></cfoutput> </cfoutput> >> >> This is now on a standalone page at: http://www.020.com/testsql.cfm >> >> The results are correct but instead of putting all of the >> articles under the corresponding author it is putting >> splitting them up and creating duplicate authors. >> >> Could it have anything to do with my tables in MS-SQL? Is >> there something else I have to do like do actual JOINS in the >> DB and not just in the code? >> >> Thanks, >> >> Stuart >> >> >> >> On 29 Sep 2005, at 12:09, Adrian Lynch wrote: >> >> >>> A quick suggestion, write a test page with the minimum of code in. >>> It'll >>> make it easier for you to debug and easier for us to help. >>> >>> Ade >>> >>> -----Original Message----- >>> From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED] >>> Sent: 29 September 2005 10:50 >>> To: CF-Talk >>> Subject: INNER JOIN >>> >>> >>> Hi, >>> >>> I've been having trouble with JOINS. My query below works >>> >> but when i >> >>> try outputting my results I am getting duplicate authors (authorID). >>> >>> <cfquery name="GetAuthors" datasource="user020"> >>> SELECT >>> tbl_020authorDetails.authorID, >>> tbl_020authorDetails.authorFirstName, >>> tbl_020authorDetails.authorSurname, >>> tbl_020authorDetails.authorPhotograph, >>> tbl_020authorDetails.authorAboutMe, >>> tbl_020articleDetails.articleID, >>> tbl_020articleDetails.articleTitle >>> FROM tbl_020articleDetails >>> INNER JOIN tbl_020authorDetails ON >>> >> tbl_020articleDetails.authorID >> >>> = tbl_020authorDetails.authorID >>> WHERE tbl_020authorDetails.authorPhotograph <> '' >>> </cfquery> >>> >>> <cfoutput query="GetAuthors" group="authorID"> >>> <cfset counter = 0> >>> <div class="contributorminibox" >>> onclick="javascript:clickarticle(this)"> >>> <div class="contributorphotograph"> >>> <img >>> src="#PhotosWritersWeb##GetAuthors.authorPhotograph#"> >>> </div> >>> <div class="contributortext"> >>> #authorFirstName# #authorSurname# >>> <p><cfif authorAboutMe eq ''>I >>> >> was born >> >>> in the outer reaches of the galaxy and ever since first seeing my >>> first book had a desire to write one.<cfelse>#ParagraphFormat >>> (authorAboutMe)#</cfif></p> >>> </div> >>> <div class="contributorurls"> >>> A list of my last articles >>> <cfoutput> >>> <br /> >>> <a href="http://www.020.com/webs/02005/ >>> showArticle.cfm?id=#articleID#"><cfif len(articleTitle) gt 35>#left >>> (articleTitle, 35)#...<cfelse>#articleTitle#</cfif></a> >>> <cfset counter = counter + 1> >>> <cfif counter EQ 8> >>> </div> >>> <div class="contributorurls"> >>> </cfif> >>> </cfoutput> >>> </div> >>> </div> >>> <div class="contributorname"> >>> #authorFirstName#<br />#authorSurname# >>> </div> >>> </cfoutput> >>> >>> I tried to make the join a RIGHT JOIN and it seemed to work >>> >> but then >> >>> will I add extra criteria to the WHERE i end up getting >>> >> more duplicate >> >>> authorIDs. >>> >>> Any help would be grateful, >>> >>> Saturday >>> >>> >>> >>> >>> >>> >>> >>> >> >> >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219579 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
