Search results

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...

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.....

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...

GOT IT!!!!!!! THANK YOU.... I just extended your query...... GREAT!!!!!! SELECT Field1, ( SELECT AVG(Field1) FROM Table2 WHERE ((Field1 <> 0) AND table1.field1_id = table2.field1_id) ) As Field2 FROM Table1 PERFECT THANKS AGAIN....

thanks lagbolt, I thought that is what I need when I saw your response.... Was so happy for a sec. ;-) The thing is it is calculating avg for the whole table not for each record in the bigger query.... I want this to be calculated for each movie in my case... So show me all movies and show me...

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...

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 =...

Hi, Thank you for that. Will try now... 001 no ;-) It's just for testing duplicate tables ;-) oryginales don't have 001 ;-)

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?

HI all, It is possible to do something like this: SELECT tbl1.fld1, (AVG(tbl2.fld1) WHERE tbl2.fld1>0) FROM ...... What I mean here is that I want to calculate avg only from fields >0 but I want to list all records but some will not have AVG at all. It's list of movies and in tbl2 I keep...

Thanks, The problem is that I use the DB from the web interface..... I believe you are talking about doing it in access?

table tbl_users_comments has a score field. So everytime somebody wants to leave a comment they can but they can also add their score. Unfortunatelly when they leave just a comment the score field is being field with 0. So then whay I try to calculate the average score for each movie that field...

Hi All, I have a query which works like I want but it calculates AVG in the wrong way. SELECT tbl_movies.movie_id, tbl_movies.movie_title, tbl_movies.movie_year, tbl_movies.movie_imdb, COUNT(tbl_users_comments.movie_id) AS 'users comments', AVG(tbl_users_comments.score) AS 'users average'...

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.

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?

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...

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

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...

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...

But can I check with this two separate fields not two criteria for one field?