Is it possible to use jcr sql2 query to find pages that contain the same id value (supposed to be unique but got duplicated some how) in one of the custom page property? I have tried with this sql2 query but it fails since we can't compare a dynamic operand against a dynamic operand. Since sql2 doesn't have group by, having clauses, my choices seem to be limited. I also tried playing around with not issamenode but no luck so far.
select n1.[jcr:path], n2.[jcr:path] from [cq:PageContent] as n1 INNER JOIN [cq:PageContent] as n2 ON n1.[custom_id] = n2.[custom_id] where ISDESCENDANTNODE(n1, "/content") and ISDESCENDANTNODE(n2, "/content") and n1.[jcr:path] <> n2.[jcr:path]
So basically custom_id page property should have been unique across repository but with users copy/pasting pages, same custom_id value can be on multiple pages and we need to find these pages.