What kind of join should I use for this query?

[slaterino]

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';

[mikosiko]

 I was trying Left Joins, Inner Joins, Outer Joins and all kinds of other Joins but with no luck. 

 

why?... show the code that you used.

 

a JOIN should do the job... short example:

SELECT a.post_id,
....
....
FROM ec3_schedule AS a JOIN posts AS b ON a.post_id = b.post_id AND b.post_status = 'publish'
WHERE....
....

[slaterino]

Yes! I worked it out. I was trying all kinds of different LEFT JOINS and INNER JOINS and just couldn't really get my head around it. But worked it out now:

 

<?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';
?>

 

Thanks for your help!