Problem with MySQL JOIN query / COUNTs

Question

I am trying to display a list of cookbooks which have been tagged with a specific tag. This is working, except I cannot also display the number of times the tag has been applied to each book by different users. I would like to sort by this number so a book tagged 'baking' for example, by multiple users, would rank higher than a book tagged 'baking' by only one user.

Here is a slightly simplified list of tables:

Table 1: cookbooks

ID, Title, Author, etc.

Table 2: recipes

ID, recipe_title, recipe, cookbook_id

Table 3: recipe_reviews

ID, recipe_id, star_rating, comments

Table 4: book_tags

tag_id, tag_text

Table 5: book_user_tag

user_id, book_id, tag_id

This is the test query as currently written:

SELECT cookbooks.*, COUNT(DISTINCT recipe_reviews.ID) as numreviews, COUNT( book_user_tag.tag_id) as numtags, AVG(recipe_reviews.star_rating) as average, book_tags.tag_text 
          FROM cookbooks 
          LEFT OUTER JOIN recipes ON cookbooks.ID = recipes.cookbook_id 
          LEFT OUTER JOIN recipe_reviews ON recipes.ID = recipe_reviews.recipe_id 
          LEFT OUTER JOIN book_user_tag ON cookbooks.ID = book_user_tag.book_id
          INNER JOIN book_tags ON book_user_tag.tag_id = book_tags.tag_id
          WHERE book_user_tag.tag_id = 69
          GROUP BY cookbooks.ID
          ORDER BY numtags DESC

UPDATE: This query ended up working when I debugged it and realized that a DISTINCT was required on the second count, and that it was trying to count the wrong variable; should have been user_id.

Infotekka · Accepted Answer · 2010-11-04 20:05:46Z

1

To debug this, try a simple SELECT * and remove your GROUP BY clause and COUNT fields, this way you can see the raw data that is produced before it is grouped, you may find that your joins are not working as you expected.

answered Nov 4, 2010 at 20:05

Infotekka

10.5k2 gold badges23 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mandel Over a year ago

Good advice! I sorted it out by breaking it down to basics and realized I was doing my COUNT on the wrong variable.

Collectives™ on Stack Overflow

Problem with MySQL JOIN query / COUNTs

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related