Lets say I have two tables. The first table is called Books. It
contains the columns ID, Title, Author, and Popularity. The second is
called BookRatings. It contains the columns ID, userID, bookID, and
Popularity. Now, lets say I have two users, Jane and Joe. There are
three books in the library: About HTML, About CGI, About ASP. Jane
reads the first two books and leaves a rating of 8 and 9 respectively.
Joe reads the first and last book and leaves a rating of 7 and 10
respectively.
Now, when Joe signs in he should be able to go to a page that shows him
the books he has read and their rating. Jane should be able to do the
same thing. How can this be accomplished.
If one does...:
SQL Select * FROM Books B LEFT JOIN BookRatings BR ON B.Popularity=BR.Popularity WHERE ID=varID
One gets the results, but the database doesn't know which records to
pull, b/c both Jane and Joe have rated some of the same books.
If one does...:
SQL Select * FROM Books B LEFT JOIN BookRatings BR ON
B.Popularity=BR.Popularity WHERE ID=varID and BR.userID=varUser and
BR.bookID=varbookID
This works fine unless the user hasn't rated the book, then it gives back no answer at all.
So, what is needed is something that does something like:
SQL Select * FROM Books B LEFT JOIN BookRatings BR ON
B.Popularity=BR.Popularity WHERE ID=varID AND IF BR.userID=varUser and
BR.bookID=varBookID then these last two conditions two.
But I am at a complete lost as to how to accomplish this.
David.
-------------
- http://www.davemackey.net/" rel="nofollow - Dave Mackey - Virtual Home.
|