How to add one more table to the query

[stone28]

Hi all,

How can I connect that forth table? At the moment it just lists all comments for a particular movie. I would like to add score as well if exists. Is it possible?

 

[SOS]

Link the tbl_users_score001.user_id to the tbl_users_comments001.user_id using an Outer Join like you have to the tbl_users table.

So, why the 001 on the table names? There shouldn't be a 002 or anything.

 

[stone28]

Hi,

Thank you for that. Will try now...

001 no ;-) It's just for testing duplicate tables ;-) oryginales don't have 001 ;-)

 

[stone28]

not sure....
SELECT tbl_movies.movie_id, tbl_movies.movie_title, tbl_users_comments001.comment, tbl_users.user_fname, tbl_users_score001.score
FROM tbl_users_score001 RIGHT JOIN (tbl_users RIGHT JOIN (tbl_movies LEFT JOIN tbl_users_comments001 ON tbl_movies.movie_id = tbl_users_comments001.movie_id) ON tbl_users.user_id = tbl_users_comments001.user_id) ON tbl_users_score001.user_id = tbl_users_comments001.user_id;

I have something like that.... and it doens't work (I conected it like the tbl_users with arrow towards tbl_users_scores001.user_id

 

[SOS]

not sure....
SELECT tbl_movies.movie_id, tbl_movies.movie_title, tbl_users_comments001.comment, tbl_users.user_fname, tbl_users_score001.score
FROM tbl_users_score001 RIGHT JOIN (tbl_users RIGHT JOIN (tbl_movies LEFT JOIN tbl_users_comments001 ON tbl_movies.movie_id = tbl_users_comments001.movie_id) ON tbl_users.user_id = tbl_users_comments001.user_id) ON tbl_users_score001.user_id = tbl_users_comments001.user_id;

I have something like that.... and it doens't work (I conected it like the tbl_users with arrow towards tbl_users_scores001.user_id

Where you got the SQL string from, change anything that says RIGHT to LEFT and see what happens.

 

[stone28]

All left is not working...

Maybe that will help.... I have sometihng like that and would like to list ALL the movies and comments and scores. Of course not all movies have comment or score or either. So list of movies and if any movie has a comment or score or both then it will be displayed in the table. if not just movie title and empty cells for comment and score.

It gives me something like that but of course the movie title is repeated so many times I think because the query is created not properly. It starts with SELECT.... FROM tbl_users_scores001. Shouldn't it start with SELECT .... FROM tbl_movies ?

 

[stone28]

Please. Anybody?

Maybe I will try to explain it again.

1. I have main table with movies. tbl_movies
2. Then there is a table which holds users comments: tbl_users_comments
(movie_id, user_id, and comment)
3. And third one is table which holds users scores for movie: tbl_users_score (movie_id, user_id and score)

I would like to creat now a table listing all comments and scores for particular movie. So I choose a file and it gives me its details - movie title, comments from users and scores.

So let's say I have 2 users. User1 left comment for that movie, and User2 left comment and score for that movie... As a result I would have table with just two rows, one for the User1 and only with comment (nothing in the score field) and the other one for User2 with comment and score...

How do I have to connect these tables to achive this. The conection on the attached images might be wrong I was just trying....

Please give me some suggestions... How should I do that?

Thank you

 

[vbaInet]

So let's say I have 2 users. User1 left comment for that movie, and User2 left comment and score for that movie... As a result I would have table with just two rows, one for the User1 and only with comment (nothing in the score field) and the other one for User2 with comment and score...

What you're asking for above is illogical and cannot be achieved easily using a query. However, if you remove the userID field from the Scores table, and link movieID in Scores table to Movies table then you can have one score per movie and your many comments per movie will still apply. Does this make sense?

 

[stone28]

But what about manu users leaving their score for the movie.... Scores tablbe is exaclt the same as comments. Many people can leave score for any movie. But of course one score epr movie per user, but each movie can have number of scores <= number of users.....

 

[stone28]

Hi All,

I have attached now test db with my tables. Could somebody please help me to create relations there and query which gives you comments and scores for particular movie. So not for all movies but for example movie_id = 3 or any other....

Please I have no idea how do it... I have tried already so many ways but I am affraid I am not that good at it....

stone28

 

[SOS]

Check out the revised copy and the query:

qryMovieCommentsAndScores

see if that gets you closer.