CODEWITHSUNDEEP
mysqlsqldatabase
parisa
parisa

Reputation: 842

Comparing two COUNTS in SQL

I have the following data base:

CREATE TABLE Book (title VARCHAR(80), author VARCHAR(80), genre VARCHAR(80));
CREATE TABLE Review (reviewername VARCHAR(80), title VARCHAR(80), rating VARCHAR(10));
CREATE TABLE Reviewer (reviewername VARCHAR(80), genre VARCHAR(80));

I want to find the books reviewed by all reviewers covering the genre of the book. In other words, a title classified as genre G is in the answer iff every reviewer that covers genre G has written a review of this title. Note: if some genre G is not covered by any reviewer then all books whose genre is G should be in the answer. The answer should have one attribute title.

My idea is to count the number of reviews for each book.

Select  COUNT(B.title) FROM Book B, Review R 
where B.title=R.title Group by B.title;

And also count the number of reviewers expert for each genre:

Select COUNT(R.reviewername) FROM Review R, Reviewer V
WHERE V.reviewername=R.reviewername group by V.genre;

And then compare these two counts to see if they are equal. If they are equal it means all the experts in particular genre reviewed all books. But when I try to compare the counts follows:

 Select B.title FROM Book B 
    WHERE 
    (Select COUNT(R.reviewername)
    FROM Review R, Reviewer V 
    WHERE V.reviewername=R.reviewername  group by V.genre) = (Select  COUNT(B.title)
    FROM Book B, Review R  
    where B.title=R.title Group by B.title);

I get error:

Subquery returns more than 1 row

Note that I don't want to use JOIN.

Sample Data and Table Definition:

CREATE TABLE Book (title VARCHAR(80), author 
VARCHAR(80), genre VARCHAR(80));
CREATE TABLE Review (reviewername VARCHAR(80), title VARCHAR(80), rating VARCHAR(10));
CREATE TABLE Reviewer (reviewername VARCHAR(80), genre VARCHAR(80));
INSERT INTO Book VALUES ("Learning SQL", "Victor", "Education");
INSERT INTO Book VALUES ("Learning SQL Part 2", "Victor", "Education");
INSERT INTO Book VALUES ("Learning SQL Part 3", "Victor", "Education");
INSERT INTO Book VALUES ("How to Save Money", "Victor", "Finance");
INSERT INTO Book VALUES ("The search for faith", "Chandler", "Religion");
INSERT INTO Review VALUES ("Ebert", "Learning SQL", "A");
INSERT INTO Review VALUES ("Ebert", "Learning SQL Part 2", "B");
INSERT INTO Review VALUES ("Hugo", "How to Save Money", "C");
INSERT INTO Review VALUES ("Todd", "How to ace the interview", "A");
INSERT INTO Review VALUES ("Todd", "How to ace the interview 2", "A");
INSERT INTO Review VALUES ("Gary", "The search for faith", "A");
INSERT INTO Review VALUES ("Jim", "Learning SQL", "B");
INSERT INTO Review VALUES ("Jim", "Learning SQL Part 3", "A");
INSERT INTO Reviewer VALUES ("Ebert", "Education");
INSERT INTO Reviewer VALUES ("Hugo", "Finance");
INSERT INTO Reviewer VALUES ("Gary", "Religion");
INSERT INTO Reviewer VALUES ("Jim", "Education");
INSERT INTO Reviewer VALUES ("Ramsey", "Finance");

Upvotes: 0

Views: 512

Answers (1)

eyalk
eyalk

Reputation: 26

You can simply use the having clause:

select b.title from book b, Review r
where b.title = r.title
group by b.title, b.genre
having count(*) = (select count(*) from reviewer where genre = b.genre);

I added the gerne to the grouping so that I'll be able to use it in the subquery. The subquery simply returns the number of reviewers in the genre.

I should point out that in you initial specs you also wanted to include books that have no reviewers in their genre. That'll probably require another subquery in the having clause.

Another note is that despite not wanting to join, you are doing a join. You're not using the keyword 'join', but you are joining book and review (the where clause is used for the join condition).

Lastly, a little unrelated, but it'll probably be a good idea to use numerical IDs for genre and reviewername since it'll make the whole thing run much faster.

Upvotes: 1

Related Questions