sql - 如何在不重复行的情况下附加来自不同表的具有匹配 ID 的数据？

Question

我正在尝试从一个表中提取数据以添加到现有查询中。本质上，我只想带入 PM 和 Supt 名称并将它们添加到具有相应员工 ID 的行中。下图中的表 1 和表 2 是我尝试从中“合并”数据的 2 个表的示例，表 3 是我希望得到的结果。

Table 1:
+--------+----------+
| Emp_ID | Emp_Name |
+--------+----------+
| 1111   | Bob      |
+--------+----------+
| 2222   | Joe      |
+--------+----------+

Table 2:
+------+------+
| PM   | Supt |
+------+------+
| 1111 | 2222 |
+------+------+
| 1111 | 3333 |
+------+------+

Table 3 (Expected Result):
+------+------+---------+-----------+
| PM   | Supt | PM_Name | Supt_Name |
+------+------+---------+-----------+
| 1111 | 2222 | Bob     | Joe       |
+------+------+---------+-----------+
| 1111 |      | Bob     | NULL      |
+------+------+---------+-----------+

到目前为止，我已经能够使用左连接和 case 语句让员工姓名显示在正确的字段中，但我得到了重复的行（每个名称实例一个）。我还包含了我当前的代码和示例结果集以供参考。

    SELECT 
                     JUDF.Job_Number, JUDF.User_Def_Sequence, JUDF.Date_Field, JUDF.Company_Code, JUDF.Alpha_Field, JM.Original_Contract, JM2.Revised_Contract, JM.Job_Description, JM.Project_Manager, JM.Superintendent, 
                     (CASE WHEN LTRIM(RTRIM(JM.Project_Manager)) = LTRIM(RTRIM(ECL.Employee_Code)) THEN ECL.Employee_Name END) AS PM_Name, (CASE WHEN LTRIM(RTRIM(JM.Superintendent)) = LTRIM(RTRIM(ECL.Employee_Code)) 
                     THEN ECL.Employee_Name END) AS Supt_Name
    FROM            dbo.JC_JOB_MASTER_MC AS JM INNER JOIN
                     dbo.JC_JOB_USER_FIELDS_DET_MC AS JUDF WITH (NOLOCK) ON JM.Company_Code = JUDF.Company_Code AND JM.Job_Number = JUDF.Job_Number INNER JOIN
                     dbo.JC_JOB_MASTER2_MC AS JM2 WITH (NOLOCK) ON JM.Company_Code = JM2.Company_Code AND JM.Job_Number = JM2.Job_Number LEFT OUTER JOIN
                     dbo.Z_EMPLOYEE_CODE_LIST AS ECL WITH (NOLOCK) ON LTRIM(RTRIM(JM.Project_Manager)) = LTRIM(RTRIM(ECL.Employee_Code)) OR
                     LTRIM(RTRIM(JM.Superintendent)) = LTRIM(RTRIM(ECL.Employee_Code))

+------------+--------------+------------+--------------+-------------+-------------------+------------------+-----------------+-----------------+----------------+---------+-----------+
| Job_Number | User_Def_Seq | Date_Field | Company_Code | Alpha_Field | Original_Contract | Revised_Contract | Job_Description | Project_Manager | Superintendent | PM_Name | Supt_Name |
+------------+--------------+------------+--------------+-------------+-------------------+------------------+-----------------+-----------------+----------------+---------+-----------+
| 12345      | 001          | NULL       | ABC          | NULL        | 12345             | 12345            | Test            | 1111            | 2222           | Bob     | NULL      |
+------------+--------------+------------+--------------+-------------+-------------------+------------------+-----------------+-----------------+----------------+---------+-----------+
| 12345      | 001          | NULL       | ABC          | NULL        | 12345             | 12345            | Test            | 1111            | 2222           | NULL    | Joe       |
+------------+--------------+------------+--------------+-------------+-------------------+------------------+-----------------+-----------------+----------------+---------+-----------+

标签： sqlsql-servertsql

Answer 1

您需要加入 ECL 表两次。一次用于 PM，一次用于 SUPTS。尝试使用单个连接OR将导致重复的行带有 NULL。