Can someone explain me how to make the join here?

Other SQL Server Topics
1

Hello

I have this database models.

Movie :

id : Integer (Primalary Key)
Name : String
Genre : String

MovieComment:

id : Integer (Primalary Key)
FilmId : Foreign key
CommentId : ForeignKey

Comment :

Id : Integer (Primalary Key)
Name: String
Comment : String
Score : Integer

Now given is a MovieId.

Now I want to find all movies which has the same score as the movieId which has given.

Can someone help me figure out and explain how to solve this with a join ?

2

If it is a one to one join

from movie 
inner join moviecomment on movie.id = moviecomment.filmid 
inner join comment on movecomment.commentid = comment.id

Since MoveiComment has id's from both tables it is used to connect them.

1 Like
3

Thanks,

If I understand you right if onlky want the titles I can do this :

SELECT title   FROM movie 
inner join moviecomment on movie.id = moviecomment.filmid 
inner join comment on movecomment.commentid = comment.id

I thought I need to use a WHERE clause.

How does this work ?

4 
SELECT title   FROM movie 
inner join moviecomment on movie.id = moviecomment.filmid 
inner join comment on movecomment.commentid = comment.id
WHERE movie.id = @MovieID;

I do not know how you are passing the movie id so I assumed a variable.

2 Likes
5 
--"Given is a MovieId,"
DECLARE @MovieId int
SET @MovieId = 23

--"I want to find all movies which has the same score as the movieId which has given."
--To do that, we first have to find the score for the given MovieId.  Since there 
--could be more than one score for a movie, I've chosen to use the highest score; you
--could instead use the low score or avg score or most recent score just as easily.

DECLARE @score_to_match int

SELECT @score_to_match = MAX(c.Score) /* or MIN() or AVG() */
FROM Movie m
INNER JOIN MovieComment mc ON mc.FilmId = m.Id
INNER JOIN Comment c ON c.Id = mc.CommentId
WHERE m.Id = @MovieId

--Now we list all movies that received the same score.  You could instead list 
--movies that had the same high, low, avg or most recent  score if you prefer.

SELECT DISTINCT m.*
FROM Movie m
INNER JOIN MovieComment mc ON mc.FilmId = m.Id
INNER JOIN Comment c ON c.Id = mc.CommentId
WHERE c.Score = @score_to_match AND
    m.Id <> @MovieId /*don't list the given movie as "matching" itself!*/