> 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).
==================================
paul,
your table joins should be inner joins since you only want to
return records that match your join criteria. also the filter
c.catid = '#variables.catid#' isn't working because you are using
AND rather than WHERE. and i assume c.catid is a string, since
you have #variable.catid# enclosed in single quotes. that's all i
see....
so i would try this:
<cfquery datasource="canadacrew" name="listimages">
SELECT i.id, i.title, i.filename, cm.name
FROM images i
INNER JOIN comments cm
ON i.id = cm.parent_id
INNER JOIN image_category c
ON c.catid = 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>
~ dina
p.s. you may want to consider buying a reference book called
"sam's teach yourself sql in 10 minutes - second edition." it's
available on forta's site:
http://www.forta.com/books/0672321289/, and i'm sure you can also
find it thru any major book outlet. i haven't read it myself, but
others on this list have given it an enthusiastic 'thumbs up.'
______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.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
