Include custom table in query

Question

Im using event calendar plugin where I can set event date for posts and it shows in calendar widget. Everything is working as inteded but I would also like to query post by event date.

Problem is event date is stored in separate table named wp_ftcalendar_events. Name of meta key is start_datetime. Table starts with post ID so it's shared for both tables. Unfortunately joining tables is beyond my knowledge.

My try doesn't work:

        <?php
        global $wpdb;
        $date = date("Y-m-d");
             $querystr = "
                SELECT $wpdb->posts.* 
                FROM $wpdb->posts, $wpdb->wp_ftcalendar_events
                WHERE $wpdb->posts.post_parent = $wpdb->wp_ftcalendar_events.post_parent 
                AND $wpdb->posts.post_status = 'publish' 
                AND $wpdb->wp_ftcalendar_events.meta_key = 'start_datetime' 
                AND $wpdb->wp_ftcalendar_events.meta_value > " . $date . " 
                ORDER BY $wpdb->wp_ftcalendar_events.meta_value ASC
             ";


             $pageposts = $wpdb->get_results($querystr, OBJECT);
             foreach ($pageposts as $post): ?>          

        <!-- do stuff -->

        <?php endforeach; ?>

Answer 1

Working code:

<?php
global $wpdb;
$date = date("Y-m-d");
$querystr = "
SELECT * 
FROM wp_posts
JOIN wp_ftcalendar_events ON wp_posts.ID = wp_ftcalendar_events.post_parent
WHERE wp_posts.post_status =  'publish'
AND wp_posts.post_type =  'post'
AND wp_ftcalendar_events.start_datetime >= '$date' 
ORDER BY wp_ftcalendar_events.start_datetime ASC 
 ";
 $pageposts = $wpdb->get_results($querystr, OBJECT_K); ?>
 <?php if ($pageposts): ?>

  <?php global $post; ?>
  <?php foreach ($pageposts as $post): ?>
      <?php setup_postdata($post); ?>

      <?php $start_date = date_i18n("Y-m-d", strtotime( $post->start_datetime )); ?>

      <?php the_title(); ?><br>
      <?php echo $date; ?><br>
      <?php echo $start_date; ?><br>
  <?php endforeach; ?>

 <?php endif; ?>