Wordpress advanced SQL - joins and counts

Question

This is how things work
I use Wordpress category tables and have 2 main categories. The first one is called "location" and the other one "subject". Both categories have sub categories of their own.

In my example we have "location" category 17 and "subject" category 3.

This is what I want to do
I want to select ONLY the data where BOTH my categories 17 AND 3 are presented.

This code works so far

SELECT term_id, post_title, post_name, ID, object_id, post_status
FROM wp_posts AS wpost
INNER JOIN wp_term_relationships
   ON wpost.ID = wp_term_relationships.object_id

INNER JOIN wp_term_taxonomy
   ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id AND wp_term_taxonomy.taxonomy = 'category'

WHERE wp_term_taxonomy.term_id IN (17, 3)
   AND post_status = 'publish'

The problem
Both categories 17 and 3 exists in the same column. The code above lists the IDs twice if the posts apears in both categories.

Is there a way to count the IDs that are equal in the result? If ID exists twice, select the post from that ID.

Answer 1

This will select each post only once, if it exists in both categories:

SELECT  post_title, post_name, post_status
FROM    wp_posts AS wpost
WHERE   post_status = 'publish'
        AND EXISTS (
        SELECT   1
        FROM     wp_term_relationships
        INNER JOIN
                 wp_term_taxonomy
        ON       wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
                 AND wp_term_taxonomy.taxonomy = 'category'
                 AND wp_term_taxonomy.term_id IN (17, 3)
        WHERE    wp_term_relationships.object_id = wpost.ID
        LIMIT 1, 1
        )

Answer 2

This would do it (provided there are no duplicate rows for an entry being in category 3 or 17) without the need for a nested query:

SELECT term_id, post_title, post_name, ID, object_id, post_status, COUNT(*) as count
FROM wp_posts AS wpost
INNER JOIN wp_term_relationships
   ON wpost.ID = wp_term_relationships.object_id

INNER JOIN wp_term_taxonomy
   ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id AND wp_term_taxonomy.taxonomy = 'category'

WHERE wp_term_taxonomy.term_id IN (17, 3)
   AND post_status = 'publish'
   AND count = 2

GROUP BY ID

Adding the count variable and the GROUP BY clause will clump duplicates together. You then filter for rows where count equals 2 to get the entries in both categories.

Answer 3

One step closer? Now the only thing I need to do is a way show the rows that contains "2" in the column "count".

Because the count column is created within the "loop" it didn't work to just write "AND count = 2".

This example displays the result including the count column:

SELECT term_id, post_title, post_name, ID, object_id, post_status, COUNT(ID) as count
FROM wp_posts AS wpost
INNER JOIN wp_term_relationships
   ON wpost.ID = wp_term_relationships.object_id

INNER JOIN wp_term_taxonomy
   ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id AND wp_term_taxonomy.taxonomy = 'category'

WHERE wp_term_taxonomy.term_id IN (17, 3)
   AND post_status = 'publish'

GROUP BY ID