Posted by ZeldorBlat on 12/15/05 05:07

Everything that's not an aggregate and appears in your select list
should also be in your group by (in general). This version should also
return all records, even when they have no comments. I did it here to
be consistent with the query you already had, but I'd advise against
the *= syntax.

Anyway, I think you want something like this:

SELECT tagCloud.id, tagCloud.url, tagCloud.item, tagCloud.headline,
tagCloud.description, tagCloud.tag1, tagCloud.tag2, tagCloud.tag3,
tagCloud.tag4, tagCloud.tag5, tagCloud.tag6, visitors.date,
comments.belongsToWhichPage, count(comment.id) howManyComments
FROM tagCloud, visitors, comments
WHERE tagCloud.visitorId = visitors.id
AND tagCloud.id *= comments.belongsToWhichPage
AND ( tag1 = 'photography' || tag2 = 'photography' || tag3 =
'photography' || tag4 = 'photography' || tag5 = 'photography' || tag6
= 'photography' )
GROUP BY tagCloud.id, tagCloud.url, tagCloud.item, tagCloud.headline,
tagCloud.description, tagCloud.tag1, tagCloud.tag2, tagCloud.tag3,
tagCloud.tag4, tagCloud.tag5, tagCloud.tag6, visitors.date,
comments.belongsToWhichPage
ORDER BY tagCloud.id DESC;

• Next in forum: Re: MySQL structure synchronization
• Prev in forum: trouble doing a join using WHERE and getting the right info when using GROUP BY
• Thread view: Re: trouble doing a join using WHERE and getting the right info when using GROUP BY

[Reply to this message]