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