How to query multiple tables with a single query

Question

I have three tables: user, friend_recommendation, and movie. I would like to get the name of the person who recommended this movie to me, along with the movie information from the movie table, and the comment in the friend_recommendation table. How do I do this?

Assume that user table has name as an attribute, movie table has movie title as an attribute, and friend_recommendation has UID (user who recommends), FID (who is recommended), and COMMENT (what is the comment from UID to FID)

Is it even possible to do this in one query?

i think you also need the film reference in friend_recommendation table — Dalen
– Dalen, Commented Apr 30, 2011 at 22:22
And the user table needs an id field not just name but apart from that just seems like you need to JOIN all three tables and give an appropriate WHERE clause. — Martin Smith
– Martin Smith, Commented Apr 30, 2011 at 22:23

nickf · Accepted Answer · 2011-05-02 09:31:06Z

2

So three tables, essentially with these fields (greatly simplified):

users (userId, name)
movies (movieId, movieInfo)
recommendations (fromUserId, toUserId, movieId, comment)

SELECT fromUser.name, toUser.name, m.movieInfo, r.comment
FROM recommendations r
INNER JOIN movies m ON m.movieId = r.movieId
INNER JOIN users fromUser ON r.fromUserId = fromUser.userId
INNER JOIN users toUser ON r.toUserId = toUser.userId
WHERE r.toUserId = << my user id, or whatever your criteria are >>

Edit in response to the comments below: Also retrieving the name/details of the user the movie was recommended to is as simple as joining to the users table again. You can see in the JOINs that we select from users twice.

edited May 2, 2011 at 9:31

answered Apr 30, 2011 at 22:26

nickf

548k199 gold badges660 silver badges727 bronze badges

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

4 Comments

alexander Over a year ago

awesome..in a rate from 0-10 how complex do you think is this query?

nickf Over a year ago

@alexander: I'd give it a 2 out of 10... why?

alexander Over a year ago

what if I want the name of both the recommender and the recommended person? I think it changes the whole thing?

alexander Over a year ago

can someone clarify my question above?

Collectives™ on Stack Overflow

How to query multiple tables with a single query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related