|
|
Posted by lkrubner on 12/15/05 05:00
I can not get the following SQL right. But then, I don't SQL that well.
If anyone can help out, I'll be grateful.
I a site where people can post links to things they like, and put tags
on those links. They can also leave comments for each of the links. I
need to get the tags, the link, the visitor information (which contains
the date the link was posted) and a count of the comments. The count of
the comments (for each link) is so that visitors can ask to see all the
links sorted by the number of comments people have made about them. In
other words, some links attract a lot of comments, and people should be
able to bring those to the top if they want.
In the following example, I'm using a particular tag: photography. On
the test site where I am working, there are 8 links that have been
tagged with the "photography" tag. So the following SQL is to get back
the 8 links that have the tag "photography", plus the date each one was
posted, plus the number of comments each link has.
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 comment 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, though 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 ;
Navigation:
[Reply to this message]
|