Stuart...
Just FYI...it'll REALLY help with readability, both for you and for us, if
you alias your tables. Like so:
You simply use the AS keyword after the table name in your FROM and INNER
JOIN statements.
FROM tablename AS alias
Or you can do it the easy way and leave off the AS keyword.
FROM tablename alias.
SELECT
auth.authorID,
auth.authorFirstName,
auth.authorSurname,
auth.authorPhotograph,
auth.authorAboutMe,
art.articleID,
art.articleTitle
FROM tbl_020articleDetails art
INNER JOIN tbl_020authorDetails auth
ON art.authorID = auth.authorID
WHERE tbl_auth.authorPhotograph <> ''
<!----------------//------
andy matthews
web developer
ICGLink, Inc.
[EMAIL PROTECTED]
615.370.1530 x737
--------------//--------->
-----Original Message-----
From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 29, 2005 4:50 AM
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:219603
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
