SQL query to get posts from multiple categories but not in a category

Question

I need to get the last posts' ID and the post_tile within categories 1 and 2, and exclude those within category 3. It must be SQL, because if I use WP_Query it retrieves too much info and I only need the ID and post_title.

The following query works fine getting posts from the 2 categories, but if I try excluding the third one, it doesn't work:

select distinct p.ID, p.post_title
            from wp_posts p, 
            wp_terms t, wp_term_taxonomy tt, wp_term_relationships tr,
            wp_terms t2, wp_term_taxonomy tt2, wp_term_relationships tr2

            where p.id = tr.object_id
            and t.term_id = tt.term_id
            and tr.term_taxonomy_id = tt.term_taxonomy_id

            and p.id = tr2.object_id
            and t2.term_id = tt2.term_id
            and tr2.term_taxonomy_id = tt2.term_taxonomy_id

            and (tt.taxonomy = 'category' and tt.term_id = t.term_id and t.term_id = 15)
            and (tt2.taxonomy = 'category' and tt2.term_id = t2.term_id and t2.term_id = 5)
            ORDER BY p.ID DESC
            LIMIT 0, 60

Any help?

Thanks in advance :)

Answer 1

You had to use LEFT JOIN to simplify the stuff. By the way, the same can be accomplished using WP_Query and I actualy don't understand why you don't want to use it.

Also I've changed the way you write the query and I've not use the hardcoded $table_prefix for answer to be useful for others too. You can replace $wpdb-> with wp_ in $query if you want to.

I assume you have third category ID=1000 (see NOT IN part):

<?php
global $wpdb;
$query = "
SELECT ID, post_title
FROM $wpdb->posts
    LEFT JOIN $wpdb->term_relationships ON (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 $wpdb->term_taxonomy.taxonomy = 'category'
    AND $wpdb->term_taxonomy.term_id IN(15, 5)
    AND $wpdb->term_taxonomy.term_id NOT IN(1000)
    ORDER BY ID DESC
    LIMIT 0, 60
";
$results = $wpdb->get_results($query);
foreach($results as $result) {
    echo $result->ID;
    echo '<br />';
    echo $result->post_title;
    echo '<hr />';
}

Answer 2

Thank you very much Steve and Max for your replys :)

I tried the query that Max answered me but it retrieves the posts that are IN (15 OR 5) AND NOT IN (1000). But what I needed is: IN (15 AND 5) AND NOT IN (1000).

Finally, I used WP_Query using the parameter 'fields' => 'ids', getting the post_title after with get_the_title($post->ID).

So, the query that finally works for me is:

<?php
$query = array(
    'post_status' => 'publish',
    'order' => 'DESC',
    'orderby' => 'ID',
    'posts_per_page' => '60',
    'post_type' => 'post',
    'category__not_in' => '47',
    'category__and' => array(5,15),
    'fields' => 'ids'
);

Thank you very much!