Custom select query two tables by a meta key

Question

I'm using an event plugin which creates wp_ftcalendar_events table in database with start_datetime column. Here's image of whole table:

I want to query my posts by start_datetime. I know that I can't do that with regular query. I also know that this query requires some conditions etc. but for a start I wanted to make simple working query and add conditions later. In codex I found this example combining 2 tables:

<?php
global $wpdb;
 $querystr = "
    SELECT * 
    FROM $wpdb->posts, $wpdb->postmeta
    WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id 
    AND $wpdb->posts.post_status = 'publish' 
    AND $wpdb->posts.post_type = 'post'
    ORDER BY $wpdb->posts.post_date DESC
 ";
 $pageposts = $wpdb->get_results($querystr, OBJECT_K); ?>
 <?php if ($pageposts): ?>

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

 <?php endif; ?>

I deleted some meta key conditions to make it simplier but it's still working. Then I changed postmeta table to ftcalendar_events table. I also changed WHERE statement because I thinks that common thing in these 2 tables is - post_parent column. I still can't make it work - it doesn't show anything. Here's whole code:

<?php
global $wpdb;
 $querystr = "
    SELECT * 
    FROM $wpdb->posts, $wpdb->ftcalendar_events
    WHERE $wpdb->posts.post_parent = $wpdb->ftcalendar_events.post_parent
    AND $wpdb->posts.post_status = 'publish' 
    AND $wpdb->posts.post_type = 'post'
    ORDER BY $wpdb->posts.post_date DESC
 ";
 $pageposts = $wpdb->get_results($querystr, OBJECT_K); ?>
 <?php if ($pageposts): ?>

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

 <?php endif; ?>

I'm not a programmer and I don't think I can push it further myself.

Screenshot of phpmyadmin query

Working code looks like this:

<?php
global $wpdb;
 $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'
ORDER BY wp_posts.post_date DESC 
 ";
 $pageposts = $wpdb->get_results($querystr, OBJECT_K); ?>
 <?php if ($pageposts): ?>

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

 <?php endif; ?>

Some result were duplicated so I changed from OBJECT to OBJECT_K. Now Im going to work on conditions.

Answer 1

First of all you are trying to combine both of the tables by post_parent.

$wpdb->posts.post_parent = $wpdb->ftcalendar_events.post_parent

I guess data in the ftcalendar_events has children of posts table, try

$wpdb->posts.ID = $wpdb->ftcalendar_events.post_parent

Also you can try joining tables:

SELECT * FROM $wpdb->posts JOIN $wpdb->ftcalendar_events
ON $wpdb->posts.ID = $wpdb->ftcalendar_events.post_parent
WHERE $wpdb->posts.post_status = 'publish' AND $wpdb->posts.post_type = 'post'
ORDER BY $wpdb->posts.post_date DESC