如何在一次查询中两次使用表中的同一字段

Question

我有两个表：呼叫和员工。在调用表中，我有一个字段enter_emp_id和另一个follow_emp_id.它们保存哪个员工输入呼叫以及哪个员工被分配到呼叫的值。我的第二个表员工有employee_id和employee字段。我能够编写一个查询来显示结果，其中包含谁进入呼叫的员工姓名，但不能显示呼叫分配给谁。我在查询中确实有其他值，但我需要获取第二个员工姓名以显示在结果中。这是我到目前为止使用的：

SELECT 
  `calls`.`call_id`,
  `calls`.`enter_date`,
  `call_state`.`call_state`,
  `customers`.`customer_name`,
  `calls`.`comments`,
  `employees`.`employee`,
  `call_reasons`.`call_reason`
FROM
  `customers`
  INNER JOIN `calls` ON (`customers`.`customer_id` = `calls`.`customer_id`)
  INNER JOIN `employees` ON (`employees`.`employee_id` = `calls`.`enter_emp_id`)
  INNER JOIN `call_state` ON (`call_state`.`call_state_id` = `calls`.`call_state_id`)
  INNER JOIN `call_reasons` ON (`call_reasons`.`call_reason_id` = `calls`.`call_reason_id`)
WHERE
  `calls`.`call_id` = $call_id;

Answer 1

您需要加入员工表两次...每个员工 ID 关联一次，并使用相应的别名获取所需的值。 并且不需要为表格添加锯齿并删除不必要的tick标记。 仅当可能的保留字冲突时才需要。

SELECT 
      c.call_id,
      c.enter_date,
      cs.call_state,
      cust.customer_name,
      c.comments,
      entered.employee,
      assigned.employee as AssignedEmployee,
      cr.call_reason
   FROM
      calls c
         INNER JOIN customers cust
            ON c.customer_id = cust.customer_id
         INNER JOIN employees entered 
            ON c.enter_emp_id = entered.employee_id 
         INNER JOIN employees assigned 
            ON c.enter_emp_id = assigned.employee_id
         INNER JOIN call_state cs 
            ON c.call_state_id = cs.call_state_id
         INNER JOIN call_reasons cr
            ON c.call_reason_id = cr.call_reason_id 
   WHERE
      c.call_id = $call_id;

Answer 2

我希望我理解你的问题。您可以使用AS更改或缩短 sql 使用的表名

  SELECT 
      `calls`.`call_id`,
      `calls`.`enter_date`,
      `call_state`.`call_state`,
      `customers`.`customer_name`,
      `calls`.`comments`,
      `employees`.`employee`,
      `assigned`.`employee` as assigned_employee,
      `call_reasons`.`call_reason`
    FROM
      `customers`
      INNER JOIN `calls` ON (`customers`.`customer_id` = `calls`.`customer_id`)
      INNER JOIN `employees` ON (`employees`.`employee_id` = `calls`.`enter_emp_id`)
      INNER JOIN `employees` AS `assigned` ON (`assigned`.`employee_id` = `calls`.`follow_emp_id`)
      INNER JOIN `call_state` ON (`call_state`.`call_state_id` = `calls`.`call_state_id`)
      INNER JOIN `call_reasons` ON (`call_reasons`.`call_reason_id` = `calls`.`call_reason_id`)
    WHERE
      `calls`.`call_id` = $call_id;

Answer 3

感谢以下工作：选择 c.call_id， c.enter_date， cs.call_state， cust.customer_name， c.评论， 输入.员工， 输入.员工， cr.call_reason 从 调用 C 内部连接客户客户 在 c.customer_id = cust.customer_id 内部加入员工进入 开c.enter_emp_id = entered.employee_id 内部联接员工分配 ON c.enter_emp_id = assigned.employee_id 内联接call_state CS 在 c.call_state_id = cs.call_state_id 内部连接call_reasons CR 在 c.call_reason_id = cr.call_reason_id 哪里 c.call_id = $call_id;