选择不在另一个表中的行，SQL Server 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8165534/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select rows not in another table, SQL Server query
提问by thewayman
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
SubjectToClass 表
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 Subjecttable where subjectidis not in SubjectToClasstable of a specified class.
我需要列出Subject表中subjectid不在SubjectToClass指定类的表中的所有行。
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)
回答by shahkalpesh
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)
回答by marc_s
You need to use the NOT INoperator - not the <>(that's VB or something....)
您需要使用NOT IN运算符 - 而不是<>（即 VB 或其他东西....）
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)
