Lonely Content Translations in Wikidata (arwiki)

Fork of Lonely Content Translations in Wikidata (arwiki) by FShbib

This query is marked as a draft This query has been published by علاء.

SQL

use arwiki_p;
select concat("[[",page_title,"]]") AS "المقالة", CONCAT("[[:d:",pp_value,"]]") AS "عنصر ويكي بيانات", REPLACE(comment_text, 'أُنشئَت بترجمة الصفحة' , '' ) AS "المقالة المُترجمة"
from revision
inner join comment
on comment_id = rev_comment_id
inner join page
on page_id = rev_page
LEFT JOIN langlinks
    	ON ll_from = page_id
	LEFT JOIN (SELECT * FROM page_props WHERE pp_propname = 'wikibase_item') AS props
		ON pp_page = page_id
where comment_text like "%أُنشئَت_بترجمة_الصفحة%"
AND ll_title IS NULL
AND page_namespace = 0
and page_is_redirect = 0
and page_namespace = 0;

SQL

Checking query status...