MySQL - query syntax

Question

I have 3 tables.

news:
id
title

categories:    
id
name

newsEachCategories:
postId
categoryId

Table newsEachCategories holds the id of each post and the id of the category in which it has been posted.

So if post with id 13 has been posted in categories 6 and 7, there are two entries, one "postId 13 , categoryId 6", and one "postId 13 , categoryId 7".

So in order to find all posts that are in a category (say category with id of 5) I use:

SELECT news.title , news.editedTitle , news.id
FROM news JOIN newsEachCategories
ON news.id = newsEachCategories.postId
WHERE  newsEachCategories.categoryId = 5

How should I synxtax my query if I want to select posts that are both categories 5 and 6?

Michael Berkowski · Accepted Answer · 2012-12-22 19:15:40Z

4

To return only results that exist in both categories, you may use a COUNT() aggregate and verify in your HAVING clause that 2 results are returned.

In this solution, I have joined against a subquery which returns the COUNT() per postId, and limits to those which are in both categories. That is joined back to the main table to get the remaining columns you wanted for that particular news.id.

SELECT 
  news.title,
  news.editedTitle,
  news.id
FROM
  news
  /* Join against subquery to count occurrences in newsEachCategories */
  JOIN (
    SELECT postId, COUNT(DISTINCT categoryId) AS postcount 
    FROM newsEachCategories 
    /* Limit to categories 5 & 6 */
    WHERE categoryId IN (5, 6)
    GROUP BY postId
    /* Ensures a result was returned for each category 5, 6 */
    HAVING postcount = 2
  ) postCounts ON news.id = postCounts.postId

answered Dec 22, 2012 at 19:15

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

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

Collectives™ on Stack Overflow

MySQL - query syntax

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related