TYPO3: Getting all the empty pages and translations of where there are no content elements defined

I'm in need of a query which gets me all the pages and translations in which no content elements are set. Since we would like to do a clean-up and the system is to large to check by hand. (40+ sys_languages x 1000's of pages)

I've come as far as to list the pages in the default language which do not have any content elements, but can't find a way to write the query because content elements are linked on the PID of the default language.

SELECT P.title, P.uid as pageUid, P.doktype
FROM pages P
LEFT JOIN tt_content C ON P.uid = C.pid and C.deleted = 0 and P.sys_language_uid = C.sys_language_uid
WHERE C.uid IS NULL and  P.deleted = 0 and P.doktype not in (199,254, 110, 102, 4, 3) and P.sys_language_uid = 0;

We use a settings called allowInconsistentLanguageHandling

• So the default language is able to be empty.
• Translations can differ from the default language.

any idea's, examples or tips are welcome.

• TYPO3(v10)
• mysql

Just a quick shot how to get empty pages (only in additional languages) without any content elements. The limit for specific pages.doktype is still missing here.

select p.uid,p.title,pl.sys_language_uid from pages p left join pages pl on pl.l10n_parent=p.uid left join tt_content c on c.pid=p.uid and c.sys_language_uid=pl.sys_language_uid where c.uid is null and pl.uid > 0 and p.deleted=0 and pl.deleted=0 and pl.hidden=0