Query for two tables

xarnet

I have two tables:

comment
id - unique comment id
topicid - the id of the topic being commented on
mid - member id
mname - membername
mgroup - membergroup
comment - the comment
date - the date in which the comment was posted (timestamp)

helpful
commentid - the id for the comment being rated
mid - memberid
mname - membername
mgroup - membergroup
helpful - was it helpful or not? (1 = yes, 0 = no)

Now what I have is the ability for members to post comments on a particular topic, whether it be a movie review or a simple article. Now this is fine and works perfectly as does the ability for members to rate whether the comment itself was helpful.

But what I want is to have only one comment displayed on the page with the topic itself. This comment is one of the following (in order of which comes first)

The most helpful comment
The last posted comment, when no comments have been rated

Now is there a way do to this?

Also note that the most helpful comment is not necessarily the comment with the most helpful votes. For example a comment that has 10 helpful votes and 10 not helpful votes is not more helpful than one with 9 helpful votes and 1 not helpful votes (ie one is 50% helpful whereas the other is 90%).

Any help on this issue would be greatly appreciated. In addition I would like to thank all those who offer a hand in advance for their time.

bubblenut

SELECT comment.* FROM comment, helpfull WHERE helpful.commentid=comment.id ORDER BY helpful DESC LIMIT 1

In order to order them by date you will need a date field in your helpful table then you would make the query

SELECT comment.* FROM comment, helpfull WHERE helpful.commentid=comment.id ORDER BY helpful.helpful DESC, helpful.date DESC LIMIT 1

HTH
Rob

bad76

Hi,
Maybe usefull GROUP by commentid and SELECT SUM(helpful) too.

SELECT c.*, sum(h.helpful) most
FROM comment c, helpful h
WHERE h.commentid=c.id
GROUP BY h.commentid
ORDER BY most DESC LIMIT 1

see you

...and for the next: don't use the same name for table & field...

xarnet

Originally posted by bad76
Hi,
Maybe usefull GROUP by commentid and SELECT SUM(helpful) too.

SELECT c.*, sum(h.helpful) most
FROM comment c, helpful h
WHERE h.commentid=c.id
GROUP BY h.commentid
ORDER BY most DESC LIMIT 1

see you

...and for the next: don't use the same name for table & field...

The table names and fields were both simply for example purposes, they are labelled different within my database, I just wanted to make it easier to understand what I wanted.

Also with the suggestion you made would it not list the comment that has the most helpful sum for example:

If a comment has 100 votes, and 25 of them are helpful, it would have a sum of 25. And it would be 25% helpful.

If a comment has 20 votes and 20 of them are helpful votes it would have a sum of 20 and it would be 100% helpful.

But with your suggestion would it not list the comment with 100 votes as the more helpful comment as it's sum is 25, when in realise the comment with 20 votes is far more helpful.

xarnet

If anyone else can offer some insight, or some help with my current problem, I would greatly appreciate it. Thanks in advance!

Danni

Well why not change your voting/rating poll:

example:

Post helpful?

op= yes
op= no

Just a suggestion.
I like simple solutions 😛

ultraslacker

Use SUM with COUNT - you could do something as simple as SUM/COUNT or you could add weight for number of comments.