Left/Inner/Outer Join - which one should I use?

Hi,
I have got a SQL query which is pulling data out of a table. However, I want the query to get data out of that table only when a criteria from another table is met.

So, I want the query to work as it is doing, but to only get fields WHERE post-status = ‘publish’ IN posts.

Both the tables ec3_schedule and post have a field called post_ID which should be joined. How do I create this join? I was trying Left Joins, Inner Joins, Outer Joins and all kinds of other Joins but with no luck.

Here is my SQL query without any of the joins:

$sql = "SELECT post_id, 
DATE_FORMAT(start,'%m/%d/%Y') AS eventStart, 
DATE_FORMAT(end,'%m/%d/%Y') AS eventEnd,
DATE_FORMAT(CURDATE(),'%m/%d/%Y') AS today,
DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 7 DAY),'%m/%d/%Y') AS endWeek 

	FROM $ec3->schedule
	WHERE ";
$clause = array();
for( $i = 0; $i < 7; $i++ ) {

$clause[] = "DATE_ADD(CURDATE(), INTERVAL $i DAY) BETWEEN DATE(start) AND DATE(end)";
}

$sql .= implode(' OR ', $clause);
$sql .= ' ORDER BY start';

select ec3_schedule.post_id, 
       <other columns>
  from ec3_schedule
  join post
    on ec3_schedule.post_id = post.post_id
 where post.status = 'Publish'
 <other conditions>

So something like this isn’t working for you?


<?php
$sql = "SELECT  post_id, 
                DATE_FORMAT(start,'%m/%d/%Y') AS eventStart, 
                DATE_FORMAT(end,'%m/%d/%Y') AS eventEnd,
                DATE_FORMAT(CURDATE(),'%m/%d/%Y') AS today,
                DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 7 DAY),'%m/%d/%Y') AS endWeek 
         FROM   $ec3->schedule s
         JOIN   $ec3->post p ON p.post_id = s.post_id
        WHERE   post-status = 'publish' ";
$clause = array();
for($i = 0; $i < 7; $i++ ) {
    $clause[] = "DATE_ADD(CURDATE(), INTERVAL $i DAY) BETWEEN DATE(start) AND DATE(end)";
}
if (count($clause) > 0) {
    $sql .= "AND (" . implode(' OR ', $clause) . ")"; 
}
$sql .= ' ORDER BY start';
?>

Whoop. That’s what I’m looking for! It was this bit:

FROM   $ec3->schedule s
         JOIN   $ec3->post p ON p.post_id = s.post_id

that was confusing me. I just couldn’t seem to get something that worked. But this is champion!

Thank you very much!