sql statement for querying wp posts from specific category

Question

i want to query wordpress posts from specific category parent but the problem is that there is no such column on wp_posts table so i need to join but my skills on the sql is not good so i need some help , here is the query i use for querying posts

    $query = "SELECT c.* 
        FROM {$wpdb->prefix}posts p,
         {$wpdb->prefix}comments c WHERE p.ID = c.comment_post_ID AND c.comment_approved > 0 AND p.post_type = 'product' AND p.post_status = 'publish' AND
          p.comment_count > 0 ORDER BY ".$order_by." LIMIT 0, ". $number_of_comments;

    }

and here is some snippet i found for joining term_taxonomy_id

$answer = $wpdb->get_results("SELECT post_title, post_content, term_taxonomy_id FROM wp_posts LEFT JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id WHERE SUBSTRING(post_title,1,1)='T' AND term_taxonomy_id=6");

the proplem i can't seem to figure how to use this example on my query neither thinking of simpler solutions so i can query from specific parent category , thanks for your help

Answer 1

here is the query i used and worked ..

$query = "SELECT  c.*
        FROM
         {$wpdb->prefix}comments c ,
         {$wpdb->prefix}posts p JOIN   $wpdb->term_relationships TR 
        ON p.ID=TR.object_id 
 JOIN   $wpdb->term_taxonomy T 
        ON TR.term_taxonomy_id=T.term_taxonomy_id
 JOIN   $wpdb->terms TS 
        ON T.term_id = TS.term_id
        WHERE p.ID = c.comment_post_ID   AND c.comment_approved > 0 AND p.post_type = 'product' AND p.post_status = 'publish' AND
          p.comment_count > 0 AND  T.taxonomy = 'product_cat' AND T.term_id='$term_cat'   ORDER BY ".$order_by." LIMIT 0, ". $number_of_comments;
     }

Answer 2

you would be looking at something like this:

global $wpdb;
$wpdb->show_errors();
$ur=$wpdb->get_results( $wpdb->prepare( 
    "
     SELECT *
     FROM $wpdb->posts P 
     JOIN   $wpdb->term_relationships TR 
            ON P.ID=TR.object_id // identify link column
     JOIN   $wpdb->term_taxonomy T 
            ON TR.term_taxonomy_id=T.term_taxonomy_id
     JOIN   $wpdb->terms TS 
            ON T.term_id = TS.term_id
     WHERE  P.post_type = %d // use wild chars (define below, this one equals carmarket)
            AND P.post_status = %f
            AND T.taxonomy= %s

    ",
        'carmarket', 
        'publish',
        'carmake'

) ); 

$wpdb->print_error();
var_dump($ur);