CODEWITHSUNDEEP
mysqlsqlwordpress
robskrob
robskrob

Reputation: 2898

SQL Select rows that do not have an association to a corresponding join table

Hi I have a SQL question. I am trying to get the rows of posts that do not have a corresponding s3 bucket row in another table. Here is the query I have which will get me the posts that have an associated row in the s3 bucket table.

SELECT * FROM wp_posts
INNER JOIN wp_as3cf_items
ON wp_as3cf_items.source_id = wp_posts.id
WHERE wp_posts.post_type = 'attachment';

What SQL can I write that will return the wp_posts rows that are of post_type attachment that do not have a an associate s3 row?

Upvotes: 0

Views: 153

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Use not exists:

SELECT p.* 
FROM wp_posts p
WHERE NOT EXISTS (SELECT 1
                  FROM wp_as3cf_items i
                  WHERE i.source_id = p.id
                 ) AND
      p.post_type = 'attachment';

Upvotes: 1

GMB
GMB

Reputation: 222432

Use not exists:

select p.*
from wp_posts p
where 
    p.post_type = 'attachement'
    and not exists (select 1 from wp_as3cf_items a where a.source_id = p.id)

Upvotes: 0

Related Questions