RE: Queries oh queries

[Andy Matthews]

I rewrote your query using the ANSI SQL method:
<cfquery name="GetAuthors">
        SELECT auth.authorID, auth.authorPhotograph, auth.authorFirstName,
auth.authorSurname, auth.authorAboutMe, art.articleID, art.articleTitle
        FROM tbl_020authorDetails auth
        INNER JOIN tbl_020articleDetails art
        WHERE authorPhotograph <> ' '
        AND art.authorID = auth.authorID
        GROUP BY auth.authorSurname,
</cfquery>

And here's how you might display them using the group attribute of the
cfquery tag:

<cfoutput query="GetAuthors" group="authorSurname">
        Author Name: #authorSurname# #authorFirstName#<br>
        <cfoutput>
        ===== #articleTitle#<br>
        </cfoutput>
</cfoutput>

<!----------------//------
andy matthews
web developer
ICGLink, Inc.
[EMAIL PROTECTED]
615.370.1530 x737
--------------//--------->

-----Original Message-----
From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 27, 2005 10:02 AM
To: CF-Talk
Subject: Queries oh queries


Hi,

I'm trying to retrieve data from two tables in one query, when i was
just querying one everything was okay:

                 <cfquery name="GetAuthors" datasource="user020">
                 SELECT authorID, authorPhotograph, authorFirstName,
authorSurname, authorAboutMe
                 FROM tbl_020authorDetails
                 WHERE authorPhotograph <> ''
                 </cfquery>

But my two table query (join) had a problem:

                 <cfquery name="GetAuthors" datasource="user020">
                 SELECT tbl_020authorDetails.authorID,
authorPhotograph, authorFirstName, authorSurname, authorAboutMe,
tbl_020articleDetails.articleID, tbl_020articleDetails.authorID,
articleTitle
                 FROM tbl_020authorDetails, tbl_020articleDetails
                 WHERE authorPhotograph <> ' ' AND
tbl_020articleDetails.authorID = tbl_020authorDetails.authorID
                 </cfquery>

The thing is that I am trying to pull all the authors and their
articles - but some authors have more than one article, is this the
best way to do it?

I'm also worried that if i pull the data like in the join above (if
that even works properly) how do i break it down, ie how do i
differentiate between getting information for individual authors?

                     <cfoutput query="GetAuthors">
                     <div class="contributorminibox"
onclick="javascript:clickarticle(this)">
                         <div class="contributorphotograph">
                             <img
src="#PhotosWritersWeb##GetAuthors.authorPhotograph#">
                         </div>
                         <div class="contributortext">
                             #authorFirstName# #authorSurname#
                             <p>#ParagraphFormat(authorAboutMe)#</p>
                         </div>
                         <div class="contributorurls">
                             The titles to go here.
                         </div>
                     </div>
                     </cfoutput>


Thanks for any help.  Should I just keep this in two queries?

Saturday







~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble 
Ticket application

http://www.houseoffusion.com/banners/view.cfm?bannerid=48

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219343
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