Connecting three tables in one query...

[stone28]

Hi All,

Hwo I can connect these three tables in one query?

tbl_movies keeps main data about the movies
movie_id (U)
movie_title
movie_year
movie_imdb
movie_entryDate

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

I have movie_id and user_id known...

So I would like to select details of movie (let say title, year, imdb) and comment for that movie from know user and the score that user gave to that movie. Ho can I do that..... I am trying and trying but I have problems with it.... The problem is I am still struggeling with the joins I think that's the reason.... Once I got that I think I would be able to do a little bit more here... Please help....

 

[namliam]

What is the commen primary/foreign key between the tables?

 

[stone28]

I mean they all have movie_id and that's how I want to select relevant fields. Is that what you are asking for?

 

[stone28]

Ok so I did it in access but now I would like to put it in one SQL query...

SELECT tbl_movies.movie_title, tbl_movies.movie_imdb, comments.comment, scores.score
FROM (tbl_movies LEFT JOIN comments ON tbl_movies.movie_id=comments.movie_id) LEFT JOIN scores ON tbl_movies.movie_id=scores.movie_id
WHERE (((tbl_movies.movie_id)=4));

but comments and scores are queries on their own:

comments:
SELECT tbl_users_comments.comment, tbl_users_comments.movie_id
FROM tbl_users_comments
WHERE (((tbl_users_comments.user_id)=1));

scores:
SELECT tbl_users_scores.score, tbl_users_scores.movie_id
FROM tbl_users_scores
WHERE (((tbl_users_scores.user_id)=1));


How can I make it into one!?

Thank you....

 

[namliam]

Have a search on the forum for something called a "search form" this will make flexible queries for you

 

[stone28]

But I just want to make it in SQL (to request result from mySQL for a webpage - I'am using ASP). Is it still something I need?

 

[namliam]

Works pretty much the same way... If you get the idea of a search form in access, you can make it in ASP

 

[stone28]

OK, I can see what is it... So it's not doable in SQL then. That's all I needed to know. I have to then solve it in a different way.