MySQL Forums
Forum List  »  Newbie

How can I use a join with possibly nonexistent rows?
Posted by: Steve Cygan
Date: March 15, 2005 02:33PM

Ok, here's the situation: I'm using mysql to house a database of quotes, complete with ratings by users. So in one query, I need to get everything with a certain rating from the "quotes" table, get the submitter's username from the "users" table and finally check the "ratings" table to see if the user viewing the quotes has already rated it.

The "ratings" table consists of the user id and the quote id. So ideally, I'll be able to to get the first 25 quotes and end up with each in a row something like this:

submitter's id - quote id - quote text - user id - quote id 2

where "user id" and "quote id 2" come from the ratings table and will be NULL if the quote hasn't been rated by the current user, and will be equal to the current user's id and the quote id if it has already been rated.

So far, I think the closest I've come to success was was using this subquery (which would probably be terribly slow even if it did work):

SELECT quotes.*, user.username, ratings.quoteid AS votedid, ratings.userid AS votedby
FROM quoteratings AS ratings
INNER JOIN quotes AS quotes
LEFT JOIN user AS user ON user.userid = quotes.userid
WHERE quotes.quoteid IN (SELECT quoteid FROM quotes WHERE average >= 0 AND approved = 1)


I hope that explanation makes sense, I know there has to be a fairly easy way to do this, but I just can't find it. Any help would be greatly appreciated.

Options: ReplyQuote


Subject
Written By
Posted
How can I use a join with possibly nonexistent rows?
March 15, 2005 02:33PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.