查询SQL Server中表的属性对

I have two tables with the following schema

我有两个表格,具有以下架构

Table A:

(Id(int PK),EmployeeId(int),DepartmentId(int))

Table B:

(Id(int PK),EmployeeId(int),DepartmentId(int))

DepartmentId,EmployeeId have 1 to 1 mapping i.e. they create a unique pair, I have some invalid mappings in table B for DepartmentId,EmployeeId I want to query all those Ids from Table B, where the same EmployeeId from Table A has a different Department Id in Table B.

DepartmentId,EmployeeId有1对1的映射,即它们创建一个唯一的对,我在表B中为DepartmentId有一些无效的映射,EmployeeId我想查询表B中的所有那些ID,其中表A中的相同EmployeeId具有不同的Department Id在表B中。

2 个解决方案

#1

Try something like this

尝试这样的事情

select * 
from tableb b 
Where exists (select 1 from tablea a 
              where a.EmployeeId = b.EmployeeId 
              and a.DepartmentId <> b.DepartmentId)

Edit : To update

编辑:更新

UPDATE b
SET    b.DepartmentId = a.DepartmentId
FROM   tablea a
       INNER JOIN tableb b
               ON a.EmployeeId = b.EmployeeId
WHERE  a.DepartmentId != b.DepartmentId

#2

select b.EmployeeId
from a
inner join b on a.EmployeeId = b.EmployeeId
where a.DepartmentId != b.DepartmentId

#1