CODEWITHSUNDEEP
mysqlwordpressjoinleft-join
Ralph M. Rivera
Ralph M. Rivera

Reputation: 789

Wordpress/MySql query is not producing all results from the LEFT part of the JOIN

I am working in a MySQL database.

I have added a custom field to a Wordpress database called "short_url"

I want a list of all posts in the database along with the value for "short_url" if the post has that value. But I want all of the posts nonetheless. I have composed the following query and used a LEFT join so that I get back all posts even if there is no corresponding custom value, but it is not working.

SELECT
    wp_posts.post_title
    , wp_posts.post_date
    , wp_postmeta.meta_value
FROM
    wp_posts
    LEFT JOIN wp_postmeta
        ON ( wp_posts.ID = wp_postmeta.post_id)
WHERE (wp_postmeta.meta_key ="short url");

This query returns 12 results, which is how many articles have the custom value, but there are 193 posts in my database.

Why I am getting back is a list of only posts that have the "short_url" value?

How can I get a full list?

Upvotes: 1

Views: 101

Answers (1)

Marcus Adams
Marcus Adams

Reputation: 53840

You need to move the condition from the WHERE clause to the ON clause. Conditions in a WHERE clause affect all of the results. Conditions in the ON clause affect the JOIN.

SELECT
    wp_posts.post_title,
    wp_posts.post_date,
    wp_postmeta.meta_value
FROM
    wp_posts
    LEFT JOIN wp_postmeta
        ON (wp_posts.ID = wp_postmeta.post_id) AND
           (wp_postmeta.meta_key ="short url");

Upvotes: 2

Related Questions