mysql - 使用多个内部联接时选择所有字段

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

我是否可以选择 c1 中的所有字段而不是只有 1 个字段？

这是我想要实现的目标

响应:

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

Answer 1

身份证名邮箱

除了 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