Count number of IDs in one column matching multiple defined IDs (rows) in another column

I’m attempting to create a reporting tool that counts the number of posts on a blog website that match BOTH of two defined taxonomy IDs (3 & 11). The data is structured so that on each row a post ID (object_id) is paired with a taxonomy ID (term_taxonomy_id). Posts are paired with multiple taxonomies, so the object_id repeats, with the different term_taxonomy_id the the next column.

Here’s a chunk of the table I’m working with:

Read More
+-----------+------------------+
| object_id | term_taxonomy_id |
+-----------+------------------+
|      4070 |               11 |
|      4070 |               19 |
|      4074 |                3 |
|      4074 |               19 |
|      4076 |                3 |
|      4076 |               10 |
|      4076 |               11 |
|      4079 |                4 |
|      4079 |               11 |
|      4079 |               16 |
|      4081 |                3 |
|      4081 |               11 |
|      4081 |               20 |
|      4083 |                3 |
|      4083 |                4 |
|      4083 |               11 |
|      4083 |               16 |
|      4083 |               21 |
+-----------+------------------+

If working correctly, the count for the above table chunk should be 3, counting object_id 4076, 4081 and 4083.

I can create a list of the object_ids I want to count using this query:

SELECT object_id
FROM test_term_relationships
WHERE test_term_relationships.term_taxonomy_id IN (3,11)
GROUP BY object_id
HAVING COUNT(*) > 1

…but I can’t work out how to count them. The methods I’ve tried from related questions here return a count of rows that match EITHER term_taxonomy_id 3 OR 11 but not BOTH (a count of 9 in this case), or they return three rows of “2” in place of the object_id to indicate the number of matched taxonomy IDs – 3 & 11.

My question: What query should I use to get achieve a correct count of posts matching BOTH term_taxonomy_id 3 & 11?

Thanks in advance.

Related posts

Leave a Reply

1 comment

  1. Just nest your query in a Derived Table:

    SELECT COUNT(*)
    FROM
     ( SELECT object_id
       FROM test_term_relationships
       WHERE test_term_relationships.term_taxonomy_id IN (3,11)
       GROUP BY object_id
       HAVING COUNT(*) > 1
     ) dt