SELECT d.* FROM Dictionary
LEFT OUTER JOIN Sam s
     ON (d.Spanish = s.Spanish)
WHERE D.Type = 'Verb'
     AND s.Spanish IS NULL

SELECT d.* FROM Dictionary
LEFT OUTER JOIN (SELECT Spanish FROM CompleteWords WHERE StudentID = 1) C
     ON (d.Spanish = c.Spanish)
WHERE d.Type = 'verb'
     AND c.Spanish IS NULL

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CompletedWords_Dictionary]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CompletedWords] DROP CONSTRAINT FK_CompletedWords_Dictionary
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CompletedWords_Students]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CompletedWords] DROP CONSTRAINT FK_CompletedWords_Students
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetWords]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetWords]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CompletedWords]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CompletedWords]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Dictionary]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Dictionary]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Students]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Students]
GO

CREATE TABLE [dbo].[CompletedWords] (
	[StudentID] [int] NOT NULL ,
	[Spanish] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Dictionary] (
	[Spanish] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[English] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Students] (
	[StudentID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
	[StudentName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CompletedWords] WITH NOCHECK ADD 
	CONSTRAINT [PK_CompletedWords] PRIMARY KEY  CLUSTERED 
	(
		[StudentID],
		[Spanish]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Dictionary] WITH NOCHECK ADD 
	CONSTRAINT [PK_Dictionary] PRIMARY KEY  CLUSTERED 
	(
		[Spanish]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Students] WITH NOCHECK ADD 
	CONSTRAINT [PK_Students] PRIMARY KEY  CLUSTERED 
	(
		[StudentID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_Dictionary] ON [dbo].[Dictionary]([Spanish]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CompletedWords] ADD 
	CONSTRAINT [FK_CompletedWords_Dictionary] FOREIGN KEY 
	(
		[Spanish]
	) REFERENCES [dbo].[Dictionary] (
		[Spanish]
	),
	CONSTRAINT [FK_CompletedWords_Students] FOREIGN KEY 
	(
		[StudentID]
	) REFERENCES [dbo].[Students] (
		[StudentID]
	)
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_GetWords (@StudentID integer)
 AS

SELECT d.* FROM Dictionary d
LEFT OUTER JOIN (SELECT Spanish FROM CompletedWords WHERE StudentID = 1) c
     ON d.Spanish = c.Spanish
WHERE d.Type = 'verb' 
    AND c.Spanish IS NULL
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

quote:

---

1. What if there were to be thousands, or tens of thousands of students, with each student having thousands of records. Would the overhead of carting around and sifting through the data then justify the extra table? Would SQLServer choke on a database with thousands of tables?

---