What's wrong...(Left, Right joins)
HI all,
I know I messed up here but I have no idea what's wrong. I think that query is above me. I was adding and adding stuff to it and now I don't know what going on here...
SELECT tbl_movies.movie_title AS 'Movie Title', tbl_movies.movie_year AS Year, tbl_movies.movie_imdb AS IMDB, "&_
"tbl_movies_genre.movie_genre AS 'Movie Genre', tbl_users_scores.score AS 'user score', "&_
"tbl_users_comments.comment_id, tbl_users_comments.comment AS Comment, tbl_users.user_fname AS 'User Name' "&_
"FROM (tbl_users "&_
"RIGHT JOIN (tbl_movies "&_
"LEFT JOIN tbl_users_comments ON tbl_movies.movie_id = tbl_users_comments.movie_id) "&_
"ON tbl_users.user_id = tbl_users_comments.user_id) "&_
"LEFT JOIN tbl_users_scores ON tbl_movies.movie_id = tbl_users_scores.movie_id "&_
"LEFT JOIN tbl_movies_genre ON tbl_movies.movie_id = tbl_movies_genre.movie_id "&_
"WHERE tbl_movies.movie_id='" & movieID & "';"
Hmmm, I don't even know how to start.
I want to display users scores (from tbl_users_scores table) and user comments (from tbp_users_comments table) but all these left, rights joins are confiusing me now....
That's the structure I have at the moment now.... Maybe that will give you better picture of what I am trying to do....
tbl_genre keeps all genres only
genre_id (U)
genre_name
tbl_movies keeps main data about the movies
movie_id (U)
movie_title
movie_year
movie_imdb
movie_entryDate
tbl_movies_2title keeps second movie title if exists
movie_id (I)
movie_2title
tbl_movies_genre connects movies with genre
movie_id (U)
movie_genre
tbl_users keeps main info about users
user_id (U)
user_fname
user_lname
user_email
user_password
user_admin
tbl_users_comments keeps users comments
comment_id (U)
movie_id
user_id
comment
tbl_users_scores keeps users scores
score_id
movie_id
user_id
score
All worked fine but since I've added tlb_users_scores table something is wrong..... it displays one comment for all records...
Please can you help? Or maybe you need some more info but I am not sure what?
Maybe it's better to create it as two separate queries. One to display details of selected movie and the other to display table with all user comments and scores...
HI all,
I know I messed up here but I have no idea what's wrong. I think that query is above me. I was adding and adding stuff to it and now I don't know what going on here...
SELECT tbl_movies.movie_title AS 'Movie Title', tbl_movies.movie_year AS Year, tbl_movies.movie_imdb AS IMDB, "&_
"tbl_movies_genre.movie_genre AS 'Movie Genre', tbl_users_scores.score AS 'user score', "&_
"tbl_users_comments.comment_id, tbl_users_comments.comment AS Comment, tbl_users.user_fname AS 'User Name' "&_
"FROM (tbl_users "&_
"RIGHT JOIN (tbl_movies "&_
"LEFT JOIN tbl_users_comments ON tbl_movies.movie_id = tbl_users_comments.movie_id) "&_
"ON tbl_users.user_id = tbl_users_comments.user_id) "&_
"LEFT JOIN tbl_users_scores ON tbl_movies.movie_id = tbl_users_scores.movie_id "&_
"LEFT JOIN tbl_movies_genre ON tbl_movies.movie_id = tbl_movies_genre.movie_id "&_
"WHERE tbl_movies.movie_id='" & movieID & "';"
Hmmm, I don't even know how to start.
I want to display users scores (from tbl_users_scores table) and user comments (from tbp_users_comments table) but all these left, rights joins are confiusing me now....
That's the structure I have at the moment now.... Maybe that will give you better picture of what I am trying to do....
tbl_genre keeps all genres only
genre_id (U)
genre_name
tbl_movies keeps main data about the movies
movie_id (U)
movie_title
movie_year
movie_imdb
movie_entryDate
tbl_movies_2title keeps second movie title if exists
movie_id (I)
movie_2title
tbl_movies_genre connects movies with genre
movie_id (U)
movie_genre
tbl_users keeps main info about users
user_id (U)
user_fname
user_lname
user_email
user_password
user_admin
tbl_users_comments keeps users comments
comment_id (U)
movie_id
user_id
comment
tbl_users_scores keeps users scores
score_id
movie_id
user_id
score
All worked fine but since I've added tlb_users_scores table something is wrong..... it displays one comment for all records...
Please can you help? Or maybe you need some more info but I am not sure what?
Maybe it's better to create it as two separate queries. One to display details of selected movie and the other to display table with all user comments and scores...
Last edited:
