Jump to content

Archived

This topic is now archived and is closed to further replies.

PHP Cherub

Help with SELECT query

Recommended Posts

man, you are the perfect questioner hehe. You gave plenty of info for what you needed :) I wish I could help you, but maybe I can. I was just curious, why do you have the movie and ratings tables seperated? You could save a lot of effort and processing time by putting them into 1 table. Something like...

 


users

userid          username

NOT NULL        NOT NULL

movies

movieid     moviename      movierating         userid

NOT NULL   NOT NULL        NULL                NULL

then do a select like:

 


select users.userid, users.username, movies.movieid, movies.moviename, movies.movierating from users INNER JOIN movies ON users.userid = movies.userid WHERE movies.userid = \'bobs id\' ORDER BY movies.moviename

 

But if you absolutely have to have them seperate then I would suggest this.

When you do the movie insert into the movies table, get the id and insert the id into the ratings table with the other 2 fields null. Then do a pull like:

 


select users.userid, users.username, movies.movieid, movies.moviename, ratings.rating from users INNER JOIN ratings ON users.userid = ratings.reluserid INNER JOIN movies ON ratings.relmovieid = movies.movieid WHERE ratings.rating IS NOT NULL && ratings.reluserid = \'bobs id\' ORDER BY movies.moviename

 

Hope one of those ways helps you out :)

Share this post


Link to post
Share on other sites

You need a LEFT JOIN to find unmatched records.

 

SELECT movies.movie_name FROM movies LEFT JOIN ratings

ON ratings.movie_id = movies.movie_id WHERE ratings.movie_id = NULL

 

will identify those movies which have not been reviewed. As you specifically want those not reviewed by Bob, you\'ll have to create a temp table containing just Bobs ratings and use that table instead of the ratings in the above SQL.

 

Alternatively if your db supports subqueries you can

 

SELECT movies.movie_name FROM movies WHERE movies.movie_id

NOT IN (SELECT movie_id FROM ratings WHERE user_id = $Bobs_id)

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.