Jump to content


Help with SELECT query

  • Please log in to reply
2 replies to this topic

#1 PHP Cherub

PHP Cherub
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 14 August 2003 - 09:54 PM


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

Thank you.

#2 DylanBlitz

  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts
  • LocationOC Baby!

Posted 15 August 2003 - 05:04 AM

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

usersuserid          usernameNOT NULL        NOT NULLmoviesmovieid     moviename      movierating         useridNOT 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 :)

#3 Barand

  • Moderators
  • Sen . ( ile || sei )
  • 18,023 posts

Posted 19 August 2003 - 07:11 PM

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)
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users