By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,318 Members | 2,380 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,318 IT Pros & Developers. It's quick & easy.

having count() trouble in a GROUP BY clause

P: n/a
The idea I'm trying to get at is that I want the tag info for the tag
"photography", and I want the date, and I want a count of any comments
a tag may have. This following query gets back all the info I want,
provided the tag has at least one comment. If It doesn't have any
comments, it doesn't show up in the return. But if it has a count of
zero, I just want it to come back with a count of zero, I don't want it
to disappear from the return. I'm not sure what I'm doing wrong, thogh
I'm no good at SQL so it is probably something obvious.

I've created for myself a test situation where I know there are 8
entries that have the tag "photography" and 3 of them have comments. If
I drop this line from the WHERE clause:

AND tagCloud.id = comments.belongsToWhichPage

then I get 8 entries in return, which is the correct number, but they
are all the same entry. The same entry 8 times that is. But if I
include the line above, then I only get the 3 entries that have
comments

The query and the table structure is below, any help is greatly
appreciated.
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( * ) 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 belongsToWhichPage
ORDER BY tagCloud.id DESC;

CREATE TABLE `comments` (
`id` int(11) NOT NULL auto_increment,
`mainContent` text NOT NULL,
`url` varchar(255) NOT NULL default '',
`email` varchar(255) NOT NULL default '',
`name` varchar(255) NOT NULL default '',
`visitorId` int(11) NOT NULL default '0',
`belongsToWhichPage` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=21 ;

# --------------------------------------

CREATE TABLE `tagCloud` (
`id` int(11) NOT NULL auto_increment,
`url` varchar(255) NOT NULL default '',
`item` varchar(255) NOT NULL default '',
`headline` varchar(255) NOT NULL default '',
`description` varchar(255) NOT NULL default '',
`tag1` varchar(255) NOT NULL default '',
`tag2` varchar(255) NOT NULL default '',
`tag3` varchar(255) NOT NULL default '',
`tag4` varchar(255) NOT NULL default '',
`tag5` varchar(255) NOT NULL default '',
`tag6` varchar(255) NOT NULL default '',
`fileType1` varchar(20) NOT NULL default '',
`fileType2` varchar(20) NOT NULL default '',
`email` varchar(255) NOT NULL default '',
`visitorId` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=20 ;

# ----------------------------------------

CREATE TABLE `visitors` (
`id` int(11) NOT NULL auto_increment,
`date` int(11) NOT NULL default '0',
`ipAddress` varchar(15) NOT NULL default '',
`hostname` varchar(255) NOT NULL default '',
`machineId` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=893 ;

Dec 14 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.