All geek questions in one place

Joining the same table more than once

I'm probably a little dumb, hope someone can help.

I have a simple table with two columns (ID, USERNAME).
I have a comment table for images (COMMENT, COMMENTFROM, COMMENTTO)

COMMENTFROM is the identifier of the user who made the comment. COMMENTTO is the identifier of the owner of the image to which the comment is added. Both users are stored in the USERS table.

I want to output and display lines like this

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

This puzzled me, because if I join the USERS table in the comments table:

 WHERE comments.userfrom = users.id

This gives me only one (or the other) of the two usernames that I need for each line. Is there a way to get both?

I'm not even sure how I will look for this answer on SOF, I apologize if this was answered. If someone can point me in the right direction, this will be appreciated :)

+4
source share
2 answers

You need to JOIN users table twice and give them different identifiers (aka aliases) on each JOIN in 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
+9
source

Please try the following

 SELECT CONCAT(c.comment," - to ", (SELECT USERNAME FROM user WHERE user.ID = c.COMMENTTO LIMIT 1), " - from ", (SELECT USERNAME FROM user WHERE user.ID = c.COMMENTFROM LIMIT 1)) FROM comments c
-1
source

Source: https://habr.com/ru/post/1486524/

More articles:


All Articles