Q: How do I find out how many that has rated a bookreview?

Hi!

Our users can write reviews about books. Each review is stored in the table RATE. Now we'd like to create a way for *other* people to rate the reviews by choosing if it was helpful or not. Just as you can do on Amazon.com.

We have the following tables:

create table RATE (

   ID int(10) unsigned not null auto_increment,    ISBN varchar(10) not null,
   USERID int(10) unsigned not null default '0',    MYDATE date not null default '0000-00-00',    COMMENT text,
   primary key (ID),
   unique ISBNUSER (ISBN, USERID),
   index MYDATE (MYDATE));

create table RATECOMMENT (

   RATEID int(10) unsigned not null default '0',    USERID int(10) unsigned not null default '0',    WHAT smallint(6) not null default '0',    primary key (RATEID, USERID),
   index WHAT (WHAT));

I don't know how to create the whole query. The beginning should be something like this:

SELECT USERID, MYDATE, COMMENT, COUNT(all RATECOMMENT where WHAT=1) AS NUMBER_OF_YES_VOTES, COUNT(all RATECOMMENT) AS TOTAL_NUMBER_OF_VOTES... FROM RATE WHERE ISBN = "1234567890" The result should be like this (for a singel book):

---

NUMBER_OF_YES_VOTES of TOTAL_NUMBER_OF_VOTES users think that the follwing comment is helpful

Written by: USERID, MYDATE
COMMENT Do you think this comment was helpful? [yes] [no]

etc.

If the comment has not yet been rated by someone else it still should show in the result like this: "0 of 0 userst think that the following co...".

Can someone guide me in the right direction, please?

Jacob Received on Thu Nov 07 2002 - 06:00:27 CST