Query with join from answer table and post and user 2 column with the same name-CodePudding

I retrieve from the query the username of the person who post and the person who answer username but 2 tables return a column of the username of people who post and who answer but l want to make difference between username column of the person who post and the person who answered how can I change the username column name in people who answered in the query.

The user who post have the same column username of answer username column answersusername who posted.

How can I change the name of usernames who answered to answerusername?

Or should I put 2 queries to make that happen?

The query

SELECT *
FROM [Post]
JOIN [User] ON [Post].userId = [User].id 
JOIN Answer ON [Post].id = [Answer].postId
JOIN [User] as UserAnswer on Answer.userId = [UserAnswer].id
WHERE [Post].id = 1

CodePudding user response：

The problem is that you are using the * to specify the output columns -- you just need to use the AS keyword to specify the output column name so something like this:

SELECT post.*, answer.*,
       user.username as post_username, 
       useranswer.username as answer_username
FROM [Post]
JOIN [User]  ON [Post].userId = [User].id 
JOIN Answer ON [Post].id = [Answer].postId
JOIN [User] as UserAnswer on Answer.userId = [UserAnswer].id
where [Post].id = 1