sql skip result based on value-CodePudding

Lets say I have a bunch of images/post, where i join images metadata. If _wp_attachment_image_alt does not have a value, then it should skip the result. Please note that it should skip the entire result of that post/image, and not just the row.

What I have tried is this:

SELECT 
                id, 
                post_title,
                guid,meta_key
            FROM 
                posts a 
            LEFT JOIN 
                postmeta b ON a.id = b.post_id
            WHERE
                post_type = 'attachment'
            AND
                meta_key NOT LIKE '_wp_attachment_image_alt'
            limit 20

This kinda works but not what I would like it to. It should skip the post from wcuq_post, and not just one row of wcuq_postmeta.

CodePudding user response：

SELECT id, post_title, guid, meta_key FROM posts a LEFT JOIN postmeta b ON a.id = b.post_id WHERE post_type = 'attachment' AND id IN (SELECT post_id FROM postmeta WHERE meta_key = '_wp_attachment_image_alt' AND meta_value != '') LIMIT 20

CodePudding user response：

This query will return all rows from the posts table that have a post_type of 'attachment' but will skip any rows that have an empty value for the _wp_attachment_image_alt field in the postmeta table. The subquery in the NOT IN clause checks for rows in the postmeta table that has an empty value for this field, and the outer query excludes these rows from the results.

SELECT 
    id, 
    post_title,
    guid,meta_key
FROM 
    posts a 
LEFT JOIN 
    postmeta b ON a.id = b.post_id
WHERE
    post_type = 'attachment'
AND
    id NOT IN (
        SELECT post_id FROM postmeta WHERE meta_key = '_wp_attachment_image_alt' AND meta_value = ''
    )
LIMIT 20