Select rows not in another table, SQL Server query

Question

Subject table

CREATE TABLE [dbo].[BS_Subject](      [SubjectID] [bigint] IDENTITY(1,1) NOT NULL,      [DepartmentID] [bigint] NOT NULL,      [SubjectName] [varchar](50) NOT NULL,      [SubjectDescription] [varchar](100) NULL,      [SubjectShortCode] [varchar](10) NOT NULL, CONSTRAINT [PK_Subject] PRIMARY KEY CLUSTERED  (     [SubjectID] ASC ) 

SubjectToClass table

CREATE TABLE [dbo].[BS_SubjectToClass]( [SubjectToClassID] [bigint] IDENTITY(1,1) NOT NULL, [SubjectID] [bigint] NOT NULL, [ClassID] [bigint] NOT NULL, CONSTRAINT [PK_BS_SubjectToClass] PRIMARY KEY CLUSTERED  ( [SubjectToClassID] ASC ) 

I need list all the rows in the Subject table where subjectid is not in SubjectToClass table of a specified class.

I have this but unable to go any further

select Distinct(BS_Subject.SubjectID) DepartmentID,         SubjectName, SubjectDescription,    SubjectShortCode from dbo.BS_Subject where BS_Subject.SubjectID <> ( SELECT     Distinct(BS_Subject.SubjectID) FROM         dbo.BS_Subject, dbo.BS_SubjectToClass Where      BS_Subject.SubjectID = BS_SubjectToClass.SubjectID And BS_SubjectToClass.ClassID = 2) 

Answer 1

SELECT SubjectID, DepartmentID, SubjectName, SubjectDescription, SubjectShortCode FROM BS_Subject  WHERE NOT EXISTS  (SELECT SubjectToClassID FROM BS_SubjectToClass WHERE  BS_Subject.SubjectID = BS_SubjectToClass.SubjectID AND BS_SubjectToClass.ClassID =2) 

Answer 2

You need to use the NOT IN operator - not the <> (that's VB or something....)

SELECT     DISTINCT(BS_Subject.SubjectID) DepartmentID,     SubjectName, SubjectDescription, SubjectShortCode FROM dbo.BS_Subject WHERE      BS_Subject.SubjectID NOT IN        (SELECT DISTINCT(BS_Subject.SubjectID)         FROM dbo.BS_Subject, dbo.BS_SubjectToClass         WHERE BS_Subject.SubjectID = BS_SubjectToClass.SubjectID         AND BS_SubjectToClass.ClassID = 2)