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:
+-----------+------------------+
| 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.
Just nest your query in a Derived Table: