Help with SELECT query
Posted 14 August 2003 - 09:54 PM
I have written out a quick table to demonstrate my problem.
Posted 15 August 2003 - 05:04 AM
usersuserid usernameNOT NULL NOT NULLmoviesmovieid moviename movierating useridNOT NULL NOT NULL NULL NULLthen 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
Posted 19 August 2003 - 07:11 PM
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)
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