1

I am trying to find duplicates of wordpress posts using SQL - but duplicates according to duplicate post meta - not duplicate titles. So far the closest code I could find does the opposite - it finds all the unique posts. How can I reverse this query?

SELECT id,meta_value, post_title, post_content
FROM wp_posts
LEFT JOIN wp_postmeta c ON ( wp_posts.ID = c.post_id )
WHERE post_type = 'post' AND meta_key = 'syndication_permalink' 
GROUP BY meta_value
HAVING Count(meta_value) > 1

*UPDATE sorry for being a noob at SQL.. I have added a table to show exactly what the aim is. I want to delete the duplicate posts from freelancer.com 

  post_id   meta_key                meta_value
  --------  -------------------     ----------------------------------------
  1         syndication_permalink   https://www.freelancer.com/projects/
  2         syndication_permalink   https://www.freelancer.com/projects/
  3         syndication_permalink   https://www.freelancer.com/projects/
  4         syndication_permalink   https://www.simplyhired.com/job/W6sVJ1
  5         syndication_permalink   https://www.mandy.com/uk/job/576913/junior
Benedict Harris
  • 184
  • 1
  • 9
  • 3
    update your question and add a proper data sample and the expected result .. – ScaisEdge Jun 11 '19 at 12:16
  • this question here is similar - and others managed to answer without a data table. I am not sure how to copy and past data from my sql to stackoverflow. basically I just want the reverse of whatever my code above selects... https://stackoverflow.com/questions/19682816/sql-statement-select-the-inverse-of-this-query – Benedict Harris Jun 11 '19 at 12:19
  • I want to get all results except the ones that are selected in this query.. – Benedict Harris Jun 11 '19 at 12:21
  • Incidentally, this renders as an INNER JOIN - and your use of a GROUP BY clause is somewhat suspect. – Strawberry Jun 11 '19 at 12:28

1 Answers1

0

I am not Sure, What you want as your Output. But Try this code and let me know whether it solved your problem or not. 

SELECT x.*
FROM wp_posts x
JOIN
(SELECT wp.meta_value
FROM wp_posts wp
LEFT JOIN wp_postmeta c ON ( wp.ID = c.post_id )
WHERE post_type = 'post' AND meta_key = 'syndication_permalink' 
GROUP BY wp.meta_value
HAVING Count(wp.meta_value) > 1) as y ON y.meta_value = x.meta_value

This will give all duplicate value as per column meta_value. Let me correct if I am Wrong.

Aditya Dhanraj
  • 189
  • 1
  • 1
  • 12