Select all rows where there is not a specific value in a different table

Posted by JMC Creative on Stack Overflow See other posts from Stack Overflow or by JMC Creative
Published on 2011-01-07T16:44:42Z Indexed on 2011/01/07 16:54 UTC
Read the original article Hit count: 200

Filed under:
|
|

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;
");

© Stack Overflow or respective owner

Related posts about sql

Related posts about Wordpress