选择不在另一个表中的行，SQL Server 查询 [英] Select rows not in another table, SQL Server query

问题描述

主题表

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 表

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
)

我需要列出 Subject 表中的所有行，其中 subjectid 不在指定类的 SubjectToClass 表中.

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)

推荐答案

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)

这篇关于选择不在另一个表中的行，SQL Server 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！