How to select multiple values with join in sql

Question

I have a database called "forum" with the tables:

"users", containing: user_id, username, password
"posts", containing: post_id, user_id (FK from "users"), type, title, content
"comments", containing: comment_id, user_id (FK from "users"), post_id (FK from posts"), content

At the moment, i'm using the query:

SELECT *
FROM forum.comments 
JOIN forum.posts ON (comments.post_id = posts.post_id) 
JOIN forum.users ON (posts.user_id= users.user_id)

With this query i can only display the username of the person who made the post, not the comment. How can i change this to show who made the comment, AND who made the post in the same query?

(If someething in the query is written wrong, it's just cause i had to translate it from another language, the query does return results).

was not aware of that option, thanks Gordon Linoff

Eirik
– Eirik

2016-04-13 14:47:51 +00:00
Commented Apr 13, 2016 at 14:47 — Eirik
– Eirik, Commented Apr 13, 2016 at 14:47

Kateract · Accepted Answer · 2016-04-13 14:49:03Z

4

You can join the table more than once if you alias the tables:

SELECT * FROM forum.comments AS c
JOIN forum.posts AS p ON (comments.post_id = posts.post_id)
JOIN forum.users AS u1 ON (posts.user_id = users.user_id)
JOIN forum.users AS u2 ON (comments.user_id = users.user_id)

When you do that, you can reference columns from the table by doing something like SELECT u1.username, u2.username ....

answered Apr 13, 2016 at 14:49

Kateract

8526 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eirik Over a year ago

Thank you for the quick reply! :)

Paul Spiegel Over a year ago

To increase readability i would use the aliases up (user of post) instead of u1 and uc (user of comment) instead of u2.

Collectives™ on Stack Overflow

How to select multiple values with join in sql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related