problem in check constraint - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

problem in check constraint

Author

Topic

pooja28d
Starting Member

8 Posts

Posted - 2011-03-18 : 05:31:34

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 bu both not by one of them.
So, how can i modify that query?

Thanks & regards,
Pooja.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-18 : 05:54:16

Why you are starting another thread ?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=158289

Did you try the solution given ?
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 TULPULE','HEMANT MAHTA')
GROUP BY B.booktitle having count(*) =2 -- 2 indicate the count of Reviewer

If the solution is not working let us know. We will check out and will help you. Creating duplicate thread is not recommended.

pooja28d
Starting Member

8 Posts

Posted - 2011-03-18 : 05:59:33

@pk_bohra
Actually, in my table their is no two reviewers who reviews the same book. And i don't know how to put multiple values in one column.
Your 'Group by' suggestion shows neither shows error nor output.

Thanks & regards,
Pooja.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-22 : 03:55:34

quote:
Originally posted by pooja28d

@pk_bohra
Actually, in my table their is no two reviewers who reviews the same book. And i don't know how to put multiple values in one column.
Your 'Group by' suggestion shows neither shows error nor output.

Thanks & regards,
Pooja.

To help you, we need some sample data for all the tables involved in the query along with your expected output.

Also, putting two values in a single column is not recommended.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources