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)

Might want to reword it and reformat to make some sense. – Preet Sangha Nov 17 '11 at 10:25 — Preet Sangha, Nov 17 '11 at 10:25

score 37 · Accepted Answer · answered Nov 17 '11 at 10:30

37

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)

answered Nov 17 '11 at 10:30

shahkalpesh

33,172
3
63
88

1

@tcables How does this not work in SSMS or Visual Studio? It executes perfectly for me in SSMS 2008 and Visual Studio 2012. – Chris Schiffhauer Sep 04 '13 at 19:52
SQL Studio did not like the statement after WHERE NOT EXISTS. Could be that it was just my setup, but when I replaced the column names in the below answer, it worked. – tcables Sep 10 '13 at 15:30

score 13 · Answer 2 · answered Nov 17 '11 at 10:32

13

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)

answered Nov 17 '11 at 10:32

marc_s

732,580
175
1,330
1,459

Fun fact, you can use [`<>` as well as `!=` in MS SQL](http://stackoverflow.com/questions/723195/should-i-use-or-for-not-equal-in-tsql) (although, that's still not the same as `NOT IN`). – Hannele Oct 03 '13 at 18:36

Select rows not in another table, SQL Server query

2 Answers2

Linked