CODEWITHSUNDEEP
mysql
lgt
lgt

Reputation: 1034

mysql exclude query

I have a wp_posts and wp_icl_translations tables what are related to each other on wp_posts.ID and wp_icl_translations.trid. I want to exclude all those posts what already have translations. The following query is retrieving back all those rows what I actually want to exclude from.

SELECT  p.ID, 
        p.post_title 
 FROM   wp_posts AS p 
            JOIN wp_icl_translations AS  t
             ON p.ID= t.trid 
 WHERE p.post_type = '{$type}' AND 
       p.post_status = 'publish' AND 
       t.language_code='en' AND 
       t.element_type='post_post' 
ORDER BY post_title;

Upvotes: 1

Views: 179

Answers (3)

Mister Henson
Mister Henson

Reputation: 505

I would say something like this; using the (NOT) IN operator. But there might be a better solution (the performance of this shouldn't be that good, also it's not tested).

SELECT ID, 
       post_title 
FROM   wp_posts
WHERE  ID NOT IN 
(
      SELECT p.ID
      FROM   wp_posts AS p 
      JOIN   wp_icl_translations AS  t
        ON   p.ID= t.trid 
      WHERE  p.post_type = '{$type}' AND 
             p.post_status = 'publish' AND 
             t.language_code='en' AND 
             t.element_type='post_post' 

)
ORDER BY post_title;

Upvotes: 0

xkeshav
xkeshav

Reputation: 54016

try ( not tested )

using NOT <> condition 

SELECT p.ID,p.post_title 
FROM   wp_posts AS p 
INNER JOIN wp_icl_translations AS  t
             ON p.ID= t.trid 
WHERE p.post_type = '{$type}' AND 
       p.post_status = 'publish' AND 
       t.language_code <>'en' AND 
       t.element_type='post_post' 
ORDER BY post_title;

or

LEFT JOIN with IS NULL

SELECT p.ID,p.post_title 
FROM   wp_posts AS p 
LEFT JOIN wp_icl_translations AS  t
             ON p.ID= t.trid 
WHERE p.post_type = '{$type}' AND 
       p.post_status = 'publish' AND 
       t.trid IS NULL
ORDER BY post_title;

Upvotes: 0

xdazz
xdazz

Reputation: 160833

Use LEFT JOIN and select with IS NULL.

SELECT  p.ID, 
        p.post_title 
 FROM   wp_posts AS p 
 LEFT JOIN wp_icl_translations AS  t ON p.ID= t.trid 
 WHERE  p.post_type = '{$type}' AND 
        p.post_status = 'publish' AND 
        t.trid IS NULL
ORDER BY post_title;

Upvotes: 1

Related Questions