MySql query not returning correct data

Question

I have a Wordpress MySQL database that I need to create a custom query for using SQL. I am trying to return the current event (post) where the "date_start" value is less than the current day and the "date_end" value is greater than or equal to the current day. I know that there is a data record that should be returned butnothing is being returned when I try to do a search on both "date_start" and "date_end"

This is my old SQL statement:

select ID, post_name, meta_id, meta_key, meta_value from wp_posts inner join wp_postmeta on wp_posts.ID = wp_postmeta.post_id and ((meta_key='date_end' and meta_value >= CURDATE() + interval 1 day) and (meta_key='date_start' and meta_value < CURDATE())) where post_type='programs' order by meta_value

The new SQL statement:

    SELECT
    ID,
    post_name,
    (SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.post_id = ID AND meta_key = 'date_start') AS 'date_start',
    (SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.post_id = ID AND meta_key = 'date_end') AS 'date_end'
FROM wp_posts
WHERE
    post_type = 'programs'
    AND ((CURDATE() >= (SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.post_id = ID AND meta_key = 'date_start'))
    AND (CURDATE() <= (SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.post_id = ID AND meta_key = 'date_end')))
ORDER BY `wp_posts`.`ID` ASC

And the date that is being returned is:

ID      post_name                   date_start  date_end
1221    culture-analytics           20160307    20160610
2446    culture-analytics-tutorials 20160308    20160311

I have also tried where the date_start and date_end portion of the inner join was in the WHERE clause.

IMPORTANT: wp_postmeta is a Many To 1 schema, whereas 'date_start' and 'date_end' are separate records but both point to the same PostID

What seems to be the problem.

Answer 1

You have two conflicting constraints on meta_key. Your query expects meta_key to equal both "date_start" and "date_end" at the same time.

Maybe try something like this:

SELECT
    ID,
    post_name,
    meta_id,
    (SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.post_id = ID AND meta_key = 'date_start') AS 'date_start',
    (SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.post_id = ID AND meta_key = 'date_end') AS 'date_end'
FROM wp_posts
WHERE
    post_type = 'programs'
    AND (SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.post_id = ID AND meta_key = 'date_start') < CURDATE()
    AND (SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.post_id = ID AND meta_key = 'date_end') >= (CURDATE() + INTERVAL 1 DAY)
ORDER BY 4

I don't have access to a Wordpress database so I haven't run it but it might work...