[FIXED] SQL NOT EXISTS in combination with LIKE doesn't work

Issue

I'm trying to return those posts with post_type = "attachment" , where their meta_value (url) doesn't appear in any post_content .

For now I'm trying to give the meta_value manually, which in this case is a image name speed1

I've cheked and there are some posts with the image "speed1.jpg" in the post_content.

When I select those WITH the word in the content, it works, the problem arises when I try to select everything else but those with the word in the content, using the query:

SELECT 
    i.ID,
    i.post_content,
    i.post_type
FROM
    wp_posts i
WHERE
    i.post_type = 'attachment'
    AND NOT EXISTS (SELECT * FROM wp_posts p WHERE p.post_type <> 'attachment' AND p.post_content LIKE "%speed1%")

this always returns empty. Why?

Solution

Here is the simple CTE that I am using as a crude mock of wp_posts for the following queries -

WITH wp_posts (ID, post_content, post_type, meta_value) AS (
    SELECT 1, null, 'attachment', 'speed1' UNION ALL
    SELECT 2, null, 'attachment', 'speed2' UNION ALL
    SELECT 3, null, 'attachment', 'speed3' UNION ALL
    SELECT 4, 'blah blah speed1 blah blah', 'post', null UNION ALL
    SELECT 5, 'blah blah speed3 blah blah', 'post', null
)

which looks like this as a table -

ID	post_content	post_type	meta_value
1	speed1	attachment	speed1
2	speed2	attachment	speed2
3	speed3	attachment	speed3
4	blah blah speed1 blah blah	post
5	blah blah speed3 blah blah	post

Your current sub-query in NOT EXISTS -

SELECT *
FROM wp_posts p
WHERE p.post_type <> 'attachment'
AND p.post_content LIKE "%speed1%"

always returns the row with ID 4 from my mock. This means NOT EXISTS always returns false as there is a result.

If you change the sub-query to use the meta_value from the outer query you have something which makes more sense -

SELECT 
    i.ID,
    i.post_content,
    i.post_type
FROM
    wp_posts i
WHERE
    i.post_type = 'attachment'
    AND NOT EXISTS (
        SELECT *
        FROM wp_posts p
        WHERE p.post_type <> 'attachment'
        AND p.post_content LIKE CONCAT('%', i.meta_value, '%')
    )

This returns the row with ID 2 as 'speed2' is not in the post_content of either of the two wp_posts of type 'post'. I hope you are able to make sense of this simple demo.

Answered By - nnichols