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).
>
>
>
>
>
>
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.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
