Select all rows where there is not a specific value in a different table
- by JMC Creative
This is a wordpress installation, but I think the question is more of a general sql question. First off, my sql skills are not great (I don't even know if I'm following the correct format for illustrating the db).
I've got two tables:
wp_posts :
ID (pk) | post_name | ...
----------------------------
45 | "Hello"
91 | "Funny"
16 | "Wheat"
wp_postmeta :
post_id (fk) | meta_key | meta_value | ...
------------------------------------------------
45 | "_edit_lock" | 5789789834
45 | "fizzbizz" | "foobar"
91 | "_something" | "teve"
16 | "_edit_lock" | 8798756
I would to select all rows in wp_posts where there is NO corresponding row in wp_postmeta where meta_key = "fizzbizz"
I originally had this query, but someone pointed out that it would not retrive ID = '16' in the above case. How would I correctly write the query?
$pageposts = $wpdb->get_results("
SELECT * FROM wp_posts p
JOIN wp_postmeta m
ON p.ID = m.post_id
WHERE m.meta_key <> 'fizzbizz'
ORDER BY p.post_date DESC;
");
