CODEWITHSUNDEEP
mysqlwordpressinner-join
Xaver
Xaver

Reputation: 11702

Inner JOIN missing row

First I would like to mention I have limited skills when it comes to MYSQL an JOIN. However this is what I have and what I like to achieve:

I have the default WordPress tables and like to get a result with post_name, title, status and the meta_value from a certain meta key.

This is what I have:

SELECT
    wp_posts.ID, wp_posts.post_name, wp_posts.post_title, wp_posts.post_status, wp_postmeta.meta_value
FROM wp_posts
INNER JOIN
    wp_term_relationships ON ( wp_posts.ID = wp_term_relationships.object_id ) 
INNER JOIN
    wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) 
WHERE (
        wp_term_relationships.term_taxonomy_id
        IN ( 1, 2, 3 )
    )
    AND wp_posts.post_type =  'my_post_type'
    AND (
        wp_posts.post_status
    IN (
        'my_status_1',  'my_status_2'
    )
)
AND wp_postmeta.meta_key =  'my_meta_key'
GROUP BY wp_posts.ID
ORDER BY wp_posts.ID ASC

Everything works as expected when each post has a postmeta of 'my_meta_key'. But if the 'my_meta_key' is missing the post is not in the result.

I guess it' caused by the second INNER JOIN but as mentioned I have no idea what I should it replace it with.

I'm sure it's something simple

Upvotes: 0

Views: 2389

Answers (3)

Taryn
Taryn

Reputation: 247860

Change the INNER JOIN on the wp_postmeta table to a LEFT JOIN:

LEFT JOIN wp_postmeta 
    ON ( wp_posts.ID = wp_postmeta.post_id )

And move the WHERE filter for the wp_postmeta to the JOIN condition.

So your query will be:

SELECT wp_posts.ID, wp_posts.post_name, wp_posts.post_title, wp_posts.post_status, wp_postmeta.meta_value
FROM wp_posts 
INNER JOIN wp_term_relationships 
    ON ( wp_posts.ID = wp_term_relationships.object_id ) 
LEFT JOIN wp_postmeta 
    ON ( wp_posts.ID = wp_postmeta.post_id ) 
    AND wp_postmeta.meta_key =  'my_meta_key'
WHERE wp_term_relationships.term_taxonomy_id IN ( 1, 2, 3 )
    AND wp_posts.post_type =  'my_post_type'
    AND wp_posts.post_status IN ('my_status_1',  'my_status_2')
GROUP BY wp_posts.ID
ORDER BY wp_posts.ID ASC

The INNER JOIN syntax will only return rows that match in both tables. So if you do not have a matching row, you will not get any result. By changing that to a LEFT JOIN, you will return all rows even if there is not a matching row in the wp_postmeta table. If the row does not exist, then the values from the wp_postmeta table will be null.

Upvotes: 0

xQbert
xQbert

Reputation: 35343

The following returns

  • all records from WP_Posts
  • only those records with WP_TERM_RELATIONSHIPS
  • only those records in wp_postmeta which a matching record in wp_posts.

*

SELECT wp_posts.ID, wp_posts.post_name, wp_posts.post_title, 
  wp_posts.post_status, wp_postmeta.meta_value
FROM wp_posts
LEFT JOIN wp_term_relationships 
  ON wp_posts.ID = wp_term_relationships.object_id
LEFT JOIN wp_postmeta 
  ON wp_posts.ID = wp_postmeta.post_id
WHERE wp_term_relationships.term_taxonomy_id IN ( 1, 2, 3 )
  AND wp_posts.post_type =  'my_post_type'
  AND wp_posts.post_statusIN ('my_status_1',  'my_status_2') 
  AND (wp_postmeta.meta_key =  'my_meta_key' or wp_postmeta.meta_key is null)
GROUP BY wp_posts.ID
ORDER BY wp_posts.ID ASC

you need the is null otherwise records in wp_posts without wp_postmeta data will be excluded.

Upvotes: 0

JudgeProphet
JudgeProphet

Reputation: 1729

Move the AND wp_postmeta.meta_key = 'my_meta_key' to

INNER JOIN
    wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )

Like this and change the INNER for a LEFT

    LEFT JOIN
        wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id 
        AND wp_postmeta.meta_key =  'my_meta_key')

If you put your condition in the "Where Clause" The Left join will be "overwritten". And I will add an advice. Dont use parenthesis when you dont really need it to keep your code easy to read.

Upvotes: 1

Related Questions