mysql - 如何进行 SQL 查询以从两个单独的表中获取两次连接的用户数据？

Question

我有一个评论列表。对于每条评论，都可能有子评论(有点像 Stack Overflow 中对答案的评论)。

我不确定该怎么做，是在一个查询中查询所有这些。到目前为止我有这个:

select 
  problem_id , suggester_id , solution , suggested_solution_id , 
  DAYOFMONTH(solution_date) , DAYNAME(solution_date) , YEAR(solution_date) , 
  MONTH(solution_date) , first_name , last_name , email , 
  small_thumb , mid_thumb , solution_name , suggested_solution_comment.comment 
from suggested_solutions 
left join users 
  on suggested_solutions.suggester_id = users.user_id 
left join member_photo 
  on suggested_solutions.suggester_id = member_photo.member_id 
left join suggested_solution_discussion 
  on suggested_solutions.suggested_solution_id = suggested_solution_discussion.suggested_solution_id 
left join users 
  on suggested_solution_discussion.commenter_id = users.user_id 
left join member_photo 
  on suggested_solution_discussion.suggester_id = member_photo.member_id 
where problem_id = id;

而suggested_solution_discussion是评论的子评论。所以我最终将 suggested_solution 表与 users 表连接起来，为了显示谁发表了子评论，我最终不得不将 suggested_solution_discussion 表与 users 表连接起来。

我得到这个错误:

ERROR 1066 (42000): Not unique table/alias: 'users'

进行此查询的正确方法是什么？

Answer 1

你已经加入了两次users表，需要给每一次都提供一个别名，以便于区分

...
left join users as users_1 on suggested_solutions.suggester_id = users_1.user_id  
...
left join users as users_2 on suggested_solution_discussion.commenter_id = users_2.user_id  
...