Hamed Ap Hamed Ap - 5 months ago 9
SQL Question

find duplicate row in mysql command

i have this command to get all post id of my wordpress site :

SELECT post_id
FROM `wp_postmeta`
WHERE `meta_key` LIKE 'imdbID'


and i want to get all post id that are NOT in this 3 command :

SELECT post_id
FROM `wp_postmeta`
WHERE `meta_key` LIKE '720-link' AND `meta_value` LIKE '%/%'

SELECT post_id
FROM `wp_postmeta`
WHERE `meta_key` LIKE 'linkDVD' AND `meta_value` LIKE '%/%'

SELECT ID
FROM `wp_posts`
WHERE `post_title` LIKE '%سر%' AND `post_status` LIKE 'publish'
AND `post_type` LIKE 'post'


how can i find it ?

Answer

You could use a WHERE IN along with UNION:

SELECT post_id
FROM `wp_postmeta`
WHERE `meta_key` LIKE 'imdbID' AND post_id NOT IN
(
    SELECT post_id
    FROM `wp_postmeta`
    WHERE (`meta_key` LIKE '720-link' OR `meta_key` LIKE 'linkDVD') AND
           `meta_value` LIKE '%/%'
    UNION
    SELECT ID
    FROM `wp_posts`
    WHERE `post_title` LIKE '%سر%' AND `post_status` LIKE 'publish' AND
          `post_type` LIKE 'post'
)
Comments