在使用多个内联接时选择所有字段

Question

我有张像这样的桌子

participant_coach表

id----nutrition_coach----mental_coach----movement_coach
1 ----       2       ----      5     ----      4

教练桌

id----name----email----
2 ----NAME---- @@@@ ----

通过一个Inner Join，我想得到每个教练的全部数据。

下面是我的当前查询

SELECT
    c1 AS nutrition_coach,
    c2 AS movement_coach,
    c3 AS mental_coach
FROM participant_coaches AS pc 
INNER JOIN coaches AS c1 ON pc.nutrition_coach = c1.id 
INNER JOIN coaches AS c2 ON pc.movement_coach = c2.id 
INNER JOIN coaches AS c3 ON pc.mental_coach = c3.id 
WHERE participant = + participantId

这是不起作用的，它会产生一个错误，即它不知道字段c1。但是，当我只想从我的c1表中选择1个属性时，这是可行的。

例如，这是可行的。

SELECT c1.id AS nutrition_coach, 
       c2.id AS movement_coach, 
       c3.id AS mental_coach

返回：

mental_coach: 3
movement_coach: 1
nutrition_coach: 2

无论如何，我可以从只有1?**的c1**instead中选择中的所有字段。

以下是我想要达到的目标

响应：

    {
       mental_coach: {
          id: 1
          name: 'MYNAME'
          email '@@@@'
       }
       movement_coach: {
          ...
       }
       nutrition_coach: {
          ...
       }
   }

Answer 1

id名称电子邮件

除了select之外，您的查询很好。MySQL没有表示表中整个行的“记录”的概念。您需要显式地列出这些列：

SELECT c1.id AS nutrition_coach_id,
       c1.name AS nutrition_coach_name,
       c1.email AS nutrition_coach_email,       
       c2.id AS movement_coach_id,
       c2.name AS movement_coach_name,
       c2.email AS movement_coach_email,
       c3.id AS mental_coach_id,
       c3.name AS mental_coach_name,
       c3.email AS mental_coach_email

Answer 2

select语句只引用表示表的别名，但实际上没有说明要使用哪一列。想必你想报告三种不同类型的教练的名字，在这三种不同类型的教练中，以下内容应该是有效的：

SELECT
    c1.name AS nutrition_coach,
    c1.email AS nutrition_coach_email, -- etc. for other fields
    c2.name AS movement_coach,
    c3.name AS mental_coach
FROM participant_coaches AS pc 
INNER JOIN coaches AS c1
    ON pc.nutrition_coach = c1.id 
INNER JOIN coaches AS c2
    ON pc.movement_coach = c2.id 
INNER JOIN coaches AS c3
    ON pc.mental_coach = c3.id 
WHERE participant = <participantId>