Jump to content

Help with SELECT query


PHP Cherub

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 :)

Link to comment
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)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.