Mysql Query not working

Question

I have three tables that are structured like this: http://i41.tinypic.com/2bt9aq.png

What I am trying to do is retrieve the joke id, title, and average rating of all jokes in a certain category and order them alphabetically. I have this query:

$result = mysql_query("
SELECT jokedata.id AS joke_id,
jokedata.joketitle AS joke_title,
SUM(ratings.rating) / COUNT(ratings.rating) AS average
FROM jokedata
INNER JOIN ratings ON ratings.content_type = 'joke' AND ratings.relative_id = jokedata.id
WHERE jokecategory = '$cur_category'
GROUP BY jokedata.id
ORDER BY jokedata.joketitle
LIMIT $offset, $jokes_per_page
");

However it is not selecting any jokes.

What is wrong with that query? Thankyou.

Answer 1

An inner join will not return a row if one of the joins cannot be fulfilled. So odds are good that the criteria ratings.relative_id = jokedata.id is causing the query to return 0 jokes. Try replacing the INNER JOIN with LEFT JOIN and you'll see how many of the jokedata's rows don't have matching id's in ratings.relative_id.

Answer 2

First, you probably want to use AVG() instead of SUM()/COUNT().

Your problem is the inner join - if no ratings where submitted for a joke then that joke would not be returned as only jokes with a rating value match the inner join.

I would recommend using a left join instead, or even a sub-select. While I normally prefer JOINs as they are usually faster, I would have tried something like this:

SELECT id AS joke_id,
joketitle AS joke_title,
(
    SELECT AVG(rating) AS avgrating FROM ratings
    WHERE content_type = 'joke' AND relative_id = joke_id
    GROUP BY relative_id
) AS average
FROM jokedata
WHERE jokecategory = '$cur_category'
GROUP BY id
ORDER BY joketitle
LIMIT $offset, $jokes_per_page

Answer 3

I would first narrow down the problem by taking out everything except the join and seeing what the result is:

SELECT * FROM jokedata INNER JOIN ratings ON ratings.content_type = 'joke' AND ratings.relative_id = jokedata.id

If this gives you results, I would then add back in the WHERE criteria. Do this step by step, and whenever you get a query where you have no rows returned, then look at the previous set of results, and think about what is happening when you add the additional criteria. Being able to see this "intermediate" set of results will help you identify and understand the problem.

Continue adding back to the query piece-wise until you start getting no results, at least then you've narrowed down to what aspect of the query is causing the problem.