Support » Fixing WordPress » Custom Select Query for Multiple Categories

  • I’m trying to create a query that would grab a post that exist in more than one category. (I’m making this custom because I’m pulling from the media library)

    The two category ID I want to check is 18 and 19.

    The current query I’m using:

    SELECT wposts.*
    FROM $wpdb->posts wposts
    	LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id)
    	LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    WHERE wposts.post_mime_type = 'image/jpeg'
    	AND $wpdb->term_taxonomy.taxonomy = 'category'
    	AND $wpdb->term_taxonomy.term_id IN(18)

    I want to add AND $wpdb->term_taxonomy.term_id IN(19) but that doesn’t work. I don’t work with sql, please help me WP heros!

Viewing 1 replies (of 1 total)
  • You need to define two occurrences of the term_* tables, like this (untested):

    SELECT wposts.*
    FROM $wpdb->posts wposts
    	JOIN $wpdb->term_relationships tr1 ON (wposts.ID = tr1.object_id)
    	JOIN $wpdb->term_taxonomy tt1 ON (tr1.term_taxonomy_id = tt1.term_taxonomy_id)
    	JOIN $wpdb->term_relationships tr2 ON (wposts.ID = tr2.object_id)
    	JOIN $wpdb->term_taxonomy tt2 ON (tr2.term_taxonomy_id = tt2.term_taxonomy_id)
    WHERE wposts.post_mime_type = 'image/jpeg'
    	AND tt1.taxonomy = 'category'
    	AND tt1.term_id = 18
    	AND tt2.taxonomy = 'category'
    	AND tt2.term_id = 18
Viewing 1 replies (of 1 total)
  • The topic ‘Custom Select Query for Multiple Categories’ is closed to new replies.