从两个不在第三个表中的连接表中选择行

我有三张 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
                 )

但没有成功 .

enter image description here

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)

2 years ago

您的查询的问题是它希望 null 等于 nullwhich is not how null works .

要更正此问题,只需将 t3.EmployeeID = t1.EmployeeID 更改为 (t3.EmployeeID = t1.EmployeeID or t1.EmployeeId is null) 即可 .

查询变为:

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 or t1.EmployeeId is null)
    and t3.AllowanceID = t2.AllowanceID
  )

rextester演示:http://rextester.com/OPRHN52820

+------------+-------------+
| EmployeeID | AllowanceID |
+------------+-------------+
|     352395 |          32 |
|    3523991 |          37 |
+------------+-------------+

2 years ago

SELECT t1.EmployeeID, t2.AllowanceID
FROM Table1 t1
JOIN Table2 t2 ON t1.EmployeeType = t2.EmployeeType
LEFT JOIN Table3 t3 ON t3.AllowanceID = t2.AllowanceID AND t3.EmployeeID = 
t1.EmployeeID
WHERE t3.EmployeeID IS NULL

2 years ago

您可以尝试使用带有所需表的子查询和带有您不想查找记录的表的左连接:

Select A.EmployeeID, A.AllowanceID
FROM (SELECT t1.EmployeeID, t2.AllowanceID
From Table2 t2 Inner Join
     Table1 t1
     on t1.EmployeeType = t2.EmployeeType ) A
   LEFT JOIN t3 ON  t3.EmployeeID = A.EmployeeID and 
                    t3.AllowanceID = A.AllowanceID
WHERE t3.EmployeeID IS NULL and t3.AllowanceID IS NULL

2 years ago

如果您使用的是SQL SERVER,请使用以下查询

SELECT t1.empoyeeId, t2.AllowanceId
FROM table1 t1
INNER JOIN table2 t2 ON t2.EmployeeTypeId = t1.EmployeeTypeID 
EXCEPT 
SELECT employeeID , allowanceID
FROM table2

2 years ago

也许您可以尝试EXCEPT,使用顺序会改变结果

SELECT t1.EmployeeID , t2.AllowanceID
FROM Table2 t2
INNER JOIN Table1 t1 ON t1.EmployeeType = t2.EmployeeType
EXCEPT
SELECT t3.EmployeeID , t3.AllowanceID
FROM Table3 t3
;

SELECT t3.EmployeeID , t3.AllowanceID
FROM Table3 t3
EXCEPT
SELECT t1.EmployeeID , t2.AllowanceID
FROM Table2 t2
INNER JOIN Table1 t1 ON t1.EmployeeType = t2.EmployeeType

从EXCEPT运算符左侧的查询中返回任何不同的值,这些值也不是从右侧查询返回的 . (ms docs)