Dunno .. it is working now ... disreguard! and THANKS for all the help! Paul Giesenhagen QuillDesign ----- Original Message ----- From: "Paul Giesenhagen" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, June 03, 2002 10:49 AM Subject: Re: Outer Join grabbing too many records
> Sheesh, can't believe I didn't see that ... but when I do correct it, it > does display the images correctly, but my GROUPING Function is not working > right now. > > I am grouping on the id value and the output looks basically like this: > > <!--- Query ---> > <cfquery datasource="canadacrew" name="listimages"> > SELECT i.id, i.title,i.filename, cm.name > FROM images i > INNER JOIN image_categories c > ON i.id = c.imageid > LEFT OUTER JOIN comments cm > ON cm.parent_id = i.id > WHERE c.catid = '#variables.catid#' > GROUP BY i.id, i.title,i.filename, cm.name > ORDER BY i.postdate desc, i.title > </cfquery> > > <!--- Output grouping on ID, count the comments ---> > > <cfoutput query="listimages" group="id"> > <cfset variables.comment_count = -1> > > <cfoutput><cfset variables.comment_count = variables.comment_count + > 1></cfoutput> > > Comments (#variables.comment_count#) > > </cfoutput> > > If there are 10 comments, it just shows a 1 instead of a 10 for > variables.comment_count. > > Thank You > Paul Giesenhagen > QuillDesign > > ----- Original Message ----- > From: "stas" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Monday, June 03, 2002 10:25 AM > Subject: Re: Outer Join grabbing too many records > > > > Move your "c.catid = '#variables.catid#'" into the WHERE clause. > > > > ----- Original Message ----- > > From: "Paul Giesenhagen" <[EMAIL PROTECTED]> > > > > <cfquery datasource="canadacrew" name="listimages"> > > SELECT i.id, i.title,i.filename, cm.name > > FROM images i > > INNER JOIN image_categories c > > ON i.id = c.imageid > > LEFT OUTER JOIN comments cm > > ON cm.parent_id = i.id > > AND c.catid = '#variables.catid#' > > GROUP BY i.id, i.title,i.filename, cm.name > > ORDER BY i.postdate desc, i.title > > </cfquery> > > > > I am running this query on MySQL, and the problem is that no matter what > > #variables.catid# Is, it always brings up every image ... Basically I want > > > to bring up every image that is defined by the 'variables.catid' and if > > there are comments, display the comments for that image ..(Not every image > > has comments, some have 1 some have 10). > > > > > > > > > > > > > ______________________________________________________________________ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
