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.
| Author |
Topic |
|
pooja28d
Starting Member
8 Posts |
 Posted - 2011-03-18 : 05:54:51
|
| 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. Query : Find all reviewers who never reviewed their own book.I got the foll. query.select r.reviewername from reviewer rinner join books bon r.reviewerid = b.bookidinner join author a on b.bookid = a.authorid where authorname not in(books)I don not thinks it is right logic? Suggest me more solutions?Thanks & regards,Pooja. |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2011-03-18 : 09:31:34
|
your join conditions are weird.1. r.reviewerid = b.bookid -- a reviewer is not a book, right?2. b.bookid = a.authorid -- an author is also not a book.3. where authorname not in(books) -- this makes no sense, and further is not valid syntax.4. what is the DDL for the author table?what are you trying to accomplish?
elsasoft.org |
 |
|
|
|
|
|
|
|
Query on relation