BookTable
=========
book_id     author_id       title
   1		1	Of Mice and Men
   2		2	Clockwork Orange
   3		1	The Wizard of Oz
   4		3       Beyond the Infinite

ReviewTable
===========
book_id     author_id       comment
   1		2	 blah blah
   2		3	 some comment
   3		2	 wow!
   4		1	 great book

quote:

---

Originally posted by ProEdge

Hi everyone,
okay so I have a somewhat complex SQL query that I'm trying to write. Or at least it's complex for me. I'll draw out the two tables that I have:

BookTable
=========
book_id (PK)
author_id (FK)
title

ReviewTable
===========
book_id (FK)
author_id (FK)
comment

Both of these have more columns in them but I didn't include them since they're not really relevant. Don't worry about the author_id. It is coming from its own table as well. Okay now let me include some sample data:

BookTable
=========
book_id     author_id       title
   1		1	Of Mice and Men
   2		2	Clockwork Orange
   3		1	The Wizard of Oz
   4		3       Beyond the Infinite

ReviewTable
===========
book_id     author_id       comment
   1		2	 blah blah
   2		3	 some comment
   3		2	 wow!
   4		1	 great book

Okay so the Book table shows us a list of books and their respective authors. The Review table, on the other hand, shows us the same books listed in the book table but which have been reviewed by other authors. So for instance, author_id #2 has reviewed both book #1 (Of Mice and Men) and book #3 (The Wizard of Oz). What I'd like to do now is return the books from the Book table which have not been reviewed by a specific author. So in the example I just gave, the books that would be returned for author_id #2 would only include book #2 (Clockwork Orange) and book #4 (Beyond the Infinite). I hope I explained this clearly but if anyone still has more questions, please feel free to ask. Thanks for your help!

---

Select b.Title
From ReviewTable r left outer join BookTable b
 On r.book_id = b.book_id
   Where r.author_id <> 2

Select b.Title
From ReviewTable r left outer join BookTable b
 On r.book_id = b.book_id
   Where r.author_id <> 2
    And
     b.book_id = Any (Select book_id
		      From BookTable
		      Where author_id <> 2)

BookTable
=========
book_id     author_id       title
   1		1	Of Mice and Men
   2		2	Clockwork Orange
   3		1	The Wizard of Oz
   4		3       Beyond the Infinite

ReviewTable
===========
book_id     author_id       comment
   1		2	 blah blah
   2		3	 some comment
   3		2	 wow!
   4		1	 great book
   4            4        excellent
   4            8        didn't like it

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[booktable]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[booktable](
	[book_id] [int] IDENTITY(1,1) NOT NULL,
	[author_id] [int] NULL,
	[title] [nvarchar](max) NULL,
 CONSTRAINT [PK_booktable] PRIMARY KEY CLUSTERED 
(
	[book_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reviewtable]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[reviewtable](
	[book_id] [int] NULL,
	[author_id] [int] NULL,
	[comment] [nvarchar](max) NULL
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_reviewtable_booktable]') AND parent_object_id = OBJECT_ID(N'[dbo].[reviewtable]'))
ALTER TABLE [dbo].[reviewtable]  WITH CHECK ADD  CONSTRAINT [FK_reviewtable_booktable] FOREIGN KEY([book_id])
REFERENCES [dbo].[booktable] ([book_id])