MySQL 连接 : Multiple fields same name

Question

我不是 SQL 专家，但我正在使用以下 SELECT:

SELECT 
        certificate.id, 
        certificate.date_created,
        certificate.date_approved,
        certificate.notes,
        certificate.id_user_student,
        certificate.id_user_exm,
        certificate.id_user_eval,

        user.name AS student_name,
        user.name AS exm_name,
        user.name AS eval_name

        FROM certificate
        LEFT JOIN 
            user ON certificate.id_user_student = user.id AND
                    certificate.id_user_exm = user.id AND
                    certificate.id_user_eval = user.id

我的想法是能够从表 user 中获取 name 列并将其JOIN 到 certificate 放置的所有用户 ID 字段的表。

在表 certificate 中，列 id_user_student、id_user_exm、id_user_eval 包含表中可用的用户 ID user 并且我想在结果集中添加他们的名字。

我当前的查询只适用于第一个结果。谁能告诉我如何解决这个问题？

谢谢!

Answer 1

您将需要三个连接操作，一个用于每个基于 id 的名称“查找”。您需要三个对用户表的引用，每个外键引用一个，例如:

SELECT c.id
     , c.date_created
     , c.date_approved
     , c.notes
     , c.id_user_student
     , c.id_user_exm
     , c.id_user_eval
     , s.name AS student_name
     , e.name AS exm_name
     , v.name AS eval_name
  FROM certificate c
  LEFT
  JOIN user s ON s.id = c.id_user_student
  LEFT
  JOIN user e ON e.id = c.id_user_exm
  LEFT
  JOIN user v ON v.id = c.id_user_eval