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

From: John Gilson <jag_at_acm.org>
Date: Thu, 07 Nov 2002 16:07:13 GMT
Message-ID: <Rcwy9.87943$Up6.19170845@twister.nyc.rr.com>

"Jacob Nordgren" <jacob_nordgren_at_hotmail.com> wrote in message news:47c6b9be.0211070400.21a196cf_at_posting.google.com...
> 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

CREATE VIEW BookReviewStats
AS
SELECT R.ISBN,

               R.UserID,
               R.MyDate,
               R.Comment,
               COUNT(RC.RateID) AS TOTAL_NUMBER_OF_VOTES,
               SUM(CASE WHEN RC.What = 1 THEN 1 ELSE 0 END) AS
               NUMBER_OF_YES_VOTES
FROM Rate AS R
            LEFT OUTER JOIN
            RateComment AS RC
            ON R.ID = RC.RateID

GROUP BY R.ISBN, R.UserID, R.MyDate, R.Comment

For a particular book,

SELECT *
FROM BookReviewStats
WHERE ISBN = '1234567890' Regards,
jag Received on Thu Nov 07 2002 - 10:07:13 CST