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:219576 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
