title: Select rows not in another table, SQL Server query
body: <p>Subject table</p>
  
  <pre><code>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
  )
  </code></pre>
  
  <p>SubjectToClass table</p>
  
  <pre><code>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
  )
  </code></pre>
  
  <p>I need list all the rows in the <code>Subject</code> table where <code>subjectid</code> is not in <code>SubjectToClass</code> table of a specified class.</p>
  
  <p>I have this but unable to go any further</p>
  
  <pre><code>select Distinct(BS_Subject.SubjectID) DepartmentID, 
         SubjectName, SubjectDescription,    SubjectShortCode
  from dbo.BS_Subject
  where BS_Subject.SubjectID &lt;&gt; (
  SELECT     Distinct(BS_Subject.SubjectID)
  FROM         dbo.BS_Subject, dbo.BS_SubjectToClass
  Where      BS_Subject.SubjectID = BS_SubjectToClass.SubjectID
  And BS_SubjectToClass.ClassID = 2)
  </code></pre>