Query to Check Duplicated Custom Post Types based on Terms and Meta Fields

Query to Check Duplicated Custom Post Types based on Terms and Meta Fields

SELECT 
    p.ID AS post_id,
    p.post_title,
    p.post_author,
    p.post_date,
    tm1.name AS purpose,
    tm2.name AS type_of_unit,
    pm1.meta_value AS unit_number,
    pm2.meta_value AS address_from_termlocation,
    pm3.meta_value AS price,
    pm4.meta_value AS number_of_bedroom,
    pm5.meta_value AS owner_name,
    pm6.meta_value AS owner_number,
    pm7.meta_value AS area_in_sqft
FROM 
    wpd8_posts AS p
INNER JOIN 
    wpd8_term_relationships AS tr1 ON p.ID = tr1.object_id
INNER JOIN 
    wpd8_term_taxonomy AS tt1 ON tr1.term_taxonomy_id = tt1.term_taxonomy_id AND tt1.taxonomy = 'purpose'
INNER JOIN 
    wpd8_terms AS tm1 ON tt1.term_id = tm1.term_id
INNER JOIN 
    wpd8_term_relationships AS tr2 ON p.ID = tr2.object_id
INNER JOIN 
    wpd8_term_taxonomy AS tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id AND tt2.taxonomy = 'type-of-unit'
INNER JOIN 
    wpd8_terms AS tm2 ON tt2.term_id = tm2.term_id
INNER JOIN 
    wpd8_postmeta AS pm1 ON p.ID = pm1.post_id AND pm1.meta_key = 'unit-number'
INNER JOIN 
    wpd8_postmeta AS pm2 ON p.ID = pm2.post_id AND pm2.meta_key = 'address-from-termlocation'
INNER JOIN 
    wpd8_postmeta AS pm3 ON p.ID = pm3.post_id AND pm3.meta_key = 'price'
INNER JOIN 
    wpd8_postmeta AS pm4 ON p.ID = pm4.post_id AND pm4.meta_key = 'number-of-bedroom'
INNER JOIN 
    wpd8_postmeta AS pm5 ON p.ID = pm5.post_id AND pm5.meta_key = 'owner-name'
INNER JOIN 
    wpd8_postmeta AS pm6 ON p.ID = pm6.post_id AND pm6.meta_key = 'owner-number'
INNER JOIN 
    wpd8_postmeta AS pm7 ON p.ID = pm7.post_id AND pm7.meta_key = 'area-in-sqft'
WHERE 
    p.post_type = 'units-availability'
AND 
    p.post_status IN ('draft', 'publish') -- Filter out units in trash
AND (
    SELECT COUNT(*)
    FROM wpd8_posts AS sub_p
    INNER JOIN wpd8_term_relationships AS sub_tr1 ON sub_p.ID = sub_tr1.object_id
    INNER JOIN wpd8_term_taxonomy AS sub_tt1 ON sub_tr1.term_taxonomy_id = sub_tt1.term_taxonomy_id AND sub_tt1.taxonomy = 'purpose'
    INNER JOIN wpd8_terms AS sub_tm1 ON sub_tt1.term_id = sub_tm1.term_id
    INNER JOIN wpd8_term_relationships AS sub_tr2 ON sub_p.ID = sub_tr2.object_id
    INNER JOIN wpd8_term_taxonomy AS sub_tt2 ON sub_tr2.term_taxonomy_id = sub_tt2.term_taxonomy_id AND sub_tt2.taxonomy = 'type-of-unit'
    INNER JOIN wpd8_terms AS sub_tm2 ON sub_tt2.term_id = sub_tm2.term_id
    INNER JOIN wpd8_postmeta AS sub_pm1 ON sub_p.ID = sub_pm1.post_id AND sub_pm1.meta_key = 'unit-number'
    INNER JOIN wpd8_postmeta AS sub_pm2 ON sub_p.ID = sub_pm2.post_id AND sub_pm2.meta_key = 'address-from-termlocation'
    WHERE 
        sub_p.post_type = 'units-availability' AND
        sub_tm1.name = tm1.name AND 
        sub_tm2.name = tm2.name AND 
        sub_pm1.meta_value = pm1.meta_value AND
        sub_pm2.meta_value = pm2.meta_value AND
        sub_p.post_status IN ('draft', 'publish') -- Ensure both units are either in draft or publish status
) > 1
ORDER BY 
    tm1.name, tm2.name, pm1.meta_value, pm2.meta_value;