Joining on the same table more than once

Question

I'm probably being a bit dumb, hopefully someone can help.

I have a simple 2 column user table (ID, USERNAME).
I have a comments table for images (COMMENT, COMMENTFROM, COMMENTTO)

COMMENTFROM is the ID of the user who made the comment. COMMENTTO is the ID of the owner of the image that the comment was added to. Both users are held within the USERS table.

I want to pull out and display rows like this

"really nice photo" - to USERXYZ - from USER123**

This has puzzled me, because if I join the USERS table to the comments table on:

WHERE comments.userfrom = users.id

That only gets me one (or the other) of the 2 usernames I need per row. Is there a way I can get both?

I'm not even sure how I would search for this answer on SOF, apologies if it has been answered before. If anyone can point me in the right direction it would be appreciated :)

Answer 1

You need to JOIN to the users table twice, and give them different identifiers (aka aliases) on each JOIN within your SQL.

SELECT 
    comment, 
    userFrom.username AS commentFrom,
    userTo.username AS commentTo
FROM comments 
JOIN users AS userFrom ON userFrom.ID = comment.commentFrom
JOIN users AS userTo ON userTo.ID = comment.commentTo