我有三张 table :
Table1: (EmployeeID int,EmployeeType int)
Table2: (AllowanceID int,EmployeeType int)
Table3: (EmployeeID int,AllowanceID int)
我需要一个查询来从两个表(Table1,Table2)中选择不在第三个表中的连接行(表3) .
I tried:
Select t1.EmployeeID, t2.AllowanceID
From Table2 t2 Inner Join
Table1 t1
on t1.EmployeeType = t2.EmployeeType
where Not Exists (select 1
From Table3 t3
where t3.EmployeeID = t1.EmployeeID and
t3.AllowanceID = t2.AllowanceID
)
但没有成功 .
CREATE TABLE [dbo].[Table1](
[EmployeeID] [int] NULL,
[EmployeeType] [tinyint] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Table2](
[AllowanceID] [int] NOT NULL,
[EmployeeType] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Table3](
[AllowanceID] [int] NULL,
[EmployeeID] [int] NULL
) ON [PRIMARY]
Insert into Table1 (EmployeeID,EmployeeType)
Values
(352395, 10),
(352396, 16),
(352397, 15),
(352398, 10),
(3523991, 16),
(NULL, 16)
Insert into Table2 (AllowanceID, EmployeeType)
Values
(100, 50),
(30, 9),
(32, 10),
(37, 16),
(512, 28),
(6000, 10)
Insert into Table3 (AllowanceID,EmployeeID)
Values
(NULL, 352400),
(32, NULL),
(37, NULL),
(37, 352395),
(6000, 352395),
(30, 352396),
(32, 352396),
(37, 352396),
(512, 352396),
(6000, 352396),
(30, 352397),
(32, 352397),
(37, 352397),
(512, 352397),
(6000, 352397),
(30, 352398),
(32, 352398),
(37, 352398),
(512, 352398),
(6000, 352398),
(30, 352399),
(32, 352399),
(37, 352399),
(512, 352399),
(6000, 352399)
5 回答
您的查询的问题是它希望
null
等于null
,which is not how null works .要更正此问题,只需将
t3.EmployeeID = t1.EmployeeID
更改为(t3.EmployeeID = t1.EmployeeID or t1.EmployeeId is null)
即可 .查询变为:
rextester演示:http://rextester.com/OPRHN52820
您可以尝试使用带有所需表的子查询和带有您不想查找记录的表的左连接:
如果您使用的是SQL SERVER,请使用以下查询
也许您可以尝试EXCEPT,使用顺序会改变结果