WordPress advanced SQL – joins and counts

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.

Read More

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.

Related posts

Leave a Reply

3 comments

  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
            )
    
  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.

  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