problem with sql query (nested AND OR)

2 Views

I’m trying to use the following query but its not getting any records: 

SELECT wp_posts.* FROM wp_posts 
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) 
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) 
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) 
WHERE ( wp_term_relationships.term_taxonomy_id IN (92) ) 
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private' OR wp_posts.post_status = 'future') 
AND wp_postmeta.meta_key = '_inicio_date' 
AND mt1.meta_key = '_inicio_date' 
AND mt2.meta_key = '_eventtimestamp' 
AND ( (mt1.meta_value BETWEEN 20110201 AND 20110231) OR (mt2.meta_value BETWEEN 20110201 AND 20110231) ) 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date asc

But, If I do:

Read More
SELECT wp_posts.* FROM wp_posts 
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)   
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) 
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) 
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) 
WHERE ( wp_term_relationships.term_taxonomy_id IN (92) ) 
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private' OR wp_posts.post_status = 'future') 
AND wp_postmeta.meta_key = '_inicio_date' 
AND mt1.meta_key = '_inicio_date' 
AND mt2.meta_key = '_eventtimestamp' 
AND (mt1.meta_value BETWEEN 20110201 AND 20110231) 
OR (mt2.meta_value BETWEEN 20110201 AND 20110231) 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date asc

It gets records, but ignores all the previos “AND” for the records found with “OR”

How can I nest the last “AND” and “OR”?

Thanks

Post Views: 2

Related posts

Leave a Reply

2 comments

  1. It looks to me that one entry in wp_posts may not have both connected records in wp_postmeta table, so change 

    INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) 
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)

    to 

    LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) 
LEFT JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)

    One more thing: be careful when you use BETWEEN:

    For best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as ‘2001-1-1’ in a comparison to a DATE, cast the string to a DATE.

  2. It worked using this:

    SELECT wp_posts.* FROM wp_posts 
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) 
LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) 
LEFT JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) 
WHERE ( wp_term_relationships.term_taxonomy_id IN (".$categoria.") ) 
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private' OR     wp_posts.post_status = 'future') 
AND ( ( mt1.meta_key = '_incio_date' AND (mt1.meta_value BETWEEN 20110201 AND 20110231) ) OR (mt2.meta_key = '_eventtimestamp' AND (mt2.meta_value BETWEEN 20110201 AND 20110231) ) )
GROUP BY wp_posts.ID ORDER BY wp_posts.post_date asc