I have a wordpress database with a number of custom fields/metadata.
---------
wp_postmeta
---------
post_id
meta_key
meta_value
I want to find all the posts with a meta_key of 'Start Date' and a value of 'NOW()', but not the posts that have the meta_key 'Ongoing' and value of 'Yes'.
I'm a bit confused of how to join the queries:
SELECT * FROM wp_posts, wp_postmeta
WHERE wp_posts.ID = wp_postmeta.post_id
AND wp_postmeta.meta_key = 'Start Date'
AND wp_postmeta.meta_value > NOW()
with
SELECT * FROM wp_posts, wp_postmeta
WHERE wp_posts.ID = wp_postmeta.post_id
AND wp_postmeta.meta_key = 'Ongoing'
AND wp_postmeta.meta_value != 'Yes'
And potentially other queries... I think what is throwing me off is that I can't add another WHERE clause to the statement? I think I just need a prod in the right direction because I am missing some basic mysql understanding.
Thank you so much in advance!
Try this:
SELECT * FROM wp_posts, wp_postmeta
WHERE wp_posts.ID = wp_postmeta.post_id
AND ((wp_postmeta.meta_key = 'Start Date'
AND wp_postmeta.meta_value > NOW()) OR (wp_postmeta.meta_key = 'Ongoing'
AND wp_postmeta.meta_value != 'Yes'))
Try to use
SELECT * FROM wp_posts, wp_postmeta
WHERE wp_posts.ID = wp_postmeta.post_id
AND wp_postmeta.meta_key = 'Start Date'
AND wp_postmeta.meta_value > NOW() and wp_posts.ID NOT IN (select post_id from wp_postmeta
where wp_postmeta.meta_key = 'Ongoing' AND wp_postmeta.meta_value = 'Yes')
I remember being there. You don't want to know the unscalable, convoluted solution I came up with.
You're just looking for a couple of paren's and an AND with a NOT.
SELECT * FROM wp_posts, wp_postmeta
WHERE wp_posts.ID = wp_postmeta.post_id
-- parens make sure everything is grouped correctly and you don't have "and
-- meta_key - Ongoing and meta_value > NOW()
(wp_postmeta.meta_key = 'Start Date'
AND wp_postmeta.meta_value > NOW())
-- these conditions are all mutually exclusive, so they could never
-- simultaneously exist.
AND NOT
(wp_postmeta.meta_key = 'Ongoing'
AND wp_postmeta.meta_value != 'Yes')
IF you wanted to combine the results, you have the option of using OR. Just replace the AND NOT with an OR and see what happens.
You can also use UNION (though that is almost never done when OR is an option (I do seem to remember that there can be subtle performance differences, but worrying about that is often premature optimization)):
SELECT * FROM wp_posts, wp_postmeta
WHERE wp_posts.ID = wp_postmeta.post_id
wp_postmeta.meta_key = 'Start Date'
AND wp_postmeta.meta_value > NOW()
-- Distinct vs. ALL is not really relevant here because you can't have overlap
UNION DISTINCT
SELECT * FROM wp_posts, wp_postmeta
WHERE wp_posts.ID = wp_postmeta.post_id
wp_postmeta.meta_key = 'Ongoing'
AND wp_postmeta.meta_value != 'Yes'
