Constraint Problem

Question

I created the following tables and inserted few values.

create table books
(bookid integer primary key, 
 booktitle varchar(20), 
 year integer, 
 publisherid integer foreign key references publisher(publisherid), 
 price integer, 
 number integer)

create table publisher
(publisherid integer primary key, 
 publishername varchar(20)) 

create table author
(authorid integer primary key, 
 authorname varchar(20))

create table bookauthor
(bookid integer foreign key references books(bookid), 
 authorid integer references author(authorid), 
 earnings integer)

create table bookreference
(bookid integer foreign key references books(bookid), 
 referencebook varchar(20), 
 times integer)

create table reviewer
(reviewerid integer primary key, 
 reviewername varchar(20))

create table bookreview
(bookid integer foreign key references books(bookid), 
 reviewerid integer foreign key references reviewer(reviewerid), 
 score integer)

Now, I want to solve following query in SQL Server 2000.

Find all the books published in 2003 and reviewed by both ‘Sammer Tulpule’ and ‘Hemant Mahta’

So, I ran following query.

SELECT B.booktitle 
FROM bookreview BR
INNER JOIN books B ON BR.bookid = B.bookid
INNER JOIN reviewer R ON R.reviewerid = BR.reviewerid
WHERE 
   B.year=2003 
   AND R.reviewername IN('Sammer Tulphule','Hemant Mahta')

But, from this I got name of all books which published in 2003 and reviewed by one of the reviewer. Actually I want to all name of books published in 2003 and reviewed by both not by one of them.

So, how can I modify that query?

Thanks & regards, Pooja.

Answer 1

Extend your query just a little bit more. This query is faster than using self-join or exist (correlated subquery) - it only passes through the table once.

SELECT B.booktitle 
FROM bookreview BR
INNER JOIN books B ON BR.bookid = B.bookid
INNER JOIN reviewer R ON R.reviewerid = BR.reviewerid
WHERE 
   B.year=2003 
   AND R.reviewername IN('Sammer Tulphule','Hemant Mahta')
GROUP BY B.booktitle
HAVING COUNT(distinct R.reviewername) = 2

Answer 2

You need to join to the bookreview and bookreviewer tables twice (once for each required reviewer). You can use different table aliases to achieve this:

select B.booktitle 
from books B 
  join bookreview BR1 on B.bookid = BR1.bookid
  join reviewer R1 on R1.reviewerid = BR1.reviewerid 
  join bookreview BR2 on B.bookid = BR2.bookid
  join reviewer R2 on R2.reviewerid = BR2.reviewerid
where B.year = 2003 and
  R1.reviewername = 'Sammer Tulphule' and
  R2.reviewername = 'Hemant Mahta'

you could also do this with an exists clause:

select B.booktitle 
from books B 
where 
   B.year = 2003 and
   exists (select 1 
     from bookreview BR 
       join reviewer R on BR.reviewerid = R.reviewerid
     where BR.bookid = B.bookid and
        R.reviewername = 'Sammer Tulphule') and
   exists (select 1 
     from bookreview BR
       join reviewer R on BR.reviewerid = R.reviewerid
     where BR.bookid = B.bookid and
        R.reviewername = 'Hemant Mahta')

Which may be easier to understand.