CODEWITHSUNDEEP
mysqlsqlselect
Genadinik
Genadinik

Reputation: 18639

How to make an SQL query to get user data as joined twice from two separate tables?

I have a list of comments. For every comment, there might be subcomments (kind of like the comments to answers in Stack Overflow).

What I am not sure how to do, is query for all of that in one query. I have this so far:

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;

And suggested_solution_discussion is the subcomments of comments. So I end up joining the suggested_solution table with the users table, and in order to display who made the sub-comments, I end up having to join for suggested_solution_discussion table with the users table to.

And I get this error:

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

What would be the right way to make this query?

Upvotes: 1

Views: 189

Answers (2)

Andrew
Andrew

Reputation: 27294

You have joined to the users table twice and need to provide an alias to each so that they can be distinguished

...
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  
...

Upvotes: 2

aF.
aF.

Reputation: 66687

You have joined to the users table twice and need to provide an alias to each so that they can be distinguished

...
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  
...

And the same goes to the other tables. If you don't use it, then when you use, for instance, users.user_id, the oprtimizer doesn't know from which table he goes.

For more info, check THIS.

Upvotes: 1

Related Questions