Help with SELECT query

[PHP Cherub]

http://www.bralynn.com/saveme.html

 

I have written out a quick table to demonstrate my problem.

 

Thank you.

[DylanBlitz]

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

[Barand]

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)