trwiki:Articles not connected to Wikidata items by author (with several language projects suggestions)

Fork of trwiki:Articles not connected to Wikidata items by author by Evolutionoftheuniverse
This query is marked as a draft This query has been published by Evolutionoftheuniverse.
SQL

USE trwiki_p;
SET sql_mode='PIPES_AS_CONCAT';
SELECT CONCAT('[['||trpage.page_title||']]') AS page_title, 
  trpage.page_len as len,
CONCAT('[[User:'||rev_actor||']]') AS user,
CONCAT(CASE
       WHEN trpage.page_title IN (SELECT enwiki_p.page.page_title
                                  FROM enwiki_p.page
                                  JOIN trwiki_p.page
                                  ON enwiki_p.page.page_title = trwiki_p.page.page_title 
                                  AND trwiki_p.page.page_namespace = 0
                                  AND trwiki_p.page.page_is_redirect = 0
                                  WHERE enwiki_p.page.page_namespace = 0 AND 
                                  enwiki_p.page.page_is_redirect = 0
                                 )
       THEN CONCAT ('[[:en:'||trpage.page_title||'|en]] ')
       WHEN trpage.page_title IN (SELECT dewiki_p.page.page_title FROM dewiki_p.page 
                                  JOIN trwiki_p.page
                                  ON dewiki_p.page.page_title = trwiki_p.page.page_title 
                                  AND trwiki_p.page.page_namespace = 0
                                  AND trwiki_p.page.page_is_redirect = 0 
                                  WHERE dewiki_p.page.page_namespace = 0 AND 
                                  dewiki_p.page.page_is_redirect = 0
                                 )
       THEN CONCAT('[[:de:'||trpage.page_title||'|de]] ')
       WHEN trpage.page_title IN (SELECT frwiki_p.page.page_title FROM frwiki_p.page
                                  JOIN trwiki_p.page
                                  ON frwiki_p.page.page_title = trwiki_p.page.page_title 
                                  AND trwiki_p.page.page_namespace = 0
                                  AND trwiki_p.page.page_is_redirect = 0
                                  WHERE frwiki_p.page.page_namespace = 0 AND 
                                  frwiki_p.page.page_is_redirect = 0
                                 )
       THEN CONCAT('[[:fr:'||trpage.page_title||'|fr]] ')
       WHEN trpage.page_title IN (SELECT itwiki_p.page.page_title FROM itwiki_p.page 
                                  JOIN trwiki_p.page
                                  ON itwiki_p.page.page_title = trwiki_p.page.page_title 
                                  AND trwiki_p.page.page_namespace = 0
                                  AND trwiki_p.page.page_is_redirect = 0                                  
                                  WHERE itwiki_p.page.page_namespace = 0 AND 
                                  itwiki_p.page.page_is_redirect = 0
                                 )
       THEN CONCAT('[[:it:'||trpage.page_title||'|it]] ')
       WHEN trpage.page_title IN (SELECT eswiki_p.page.page_title FROM eswiki_p.page 
                                  JOIN trwiki_p.page
                                  ON eswiki_p.page.page_title = trwiki_p.page.page_title 
                                  AND trwiki_p.page.page_namespace = 0
                                  AND trwiki_p.page.page_is_redirect = 0                                  
                           WHERE eswiki_p.page.page_namespace = 0 AND 
                           eswiki_p.page.page_is_redirect = 0)
                          THEN CONCAT('[[:es:'||trpage.page_title||'|es]] ')
  WHEN trpage.page_title IN (SELECT ruwiki_p.page.page_title
FROM ruwiki_p.page
JOIN trwiki_p.page
ON ruwiki_p.page.page_title = trwiki_p.page.page_title AND trwiki_p.page.page_namespace = 0
AND trwiki_p.page.page_is_redirect = 0
                           WHERE ruwiki_p.page.page_namespace = 0 AND 
                           ruwiki_p.page.page_is_redirect = 0)
                           THEN CONCAT('[[:ru:'||trpage.page_title||'|ru]] ')
       WHEN trpage.page_title IN (SELECT nlwiki_p.page.page_title FROM nlwiki_p.page
                                  JOIN trwiki_p.page
                                  ON nlwiki_p.page.page_title = trwiki_p.page.page_title 
                                  AND trwiki_p.page.page_namespace = 0
                                  AND trwiki_p.page.page_is_redirect = 0
                                  WHERE nlwiki_p.page.page_namespace = 0 AND 
                                  nlwiki_p.page.page_is_redirect = 0
                                 )
       THEN CONCAT('[[:nl:'||trpage.page_title||'|nl]] ')
       WHEN trpage.page_title IN (SELECT azwiki_p.page.page_title FROM azwiki_p.page
                                  JOIN trwiki_p.page
                                  ON azwiki_p.page.page_title = trwiki_p.page.page_title 
                                  AND trwiki_p.page.page_namespace = 0
                                  AND trwiki_p.page.page_is_redirect = 0
                                  WHERE azwiki_p.page.page_namespace = 0 AND 
                                  azwiki_p.page.page_is_redirect = 0
                                 )
       THEN CONCAT('[[:az:'||trpage.page_title||'|az]] ')
       WHEN trpage.page_title IN (SELECT lawiki_p.page.page_title FROM lawiki_p.page
                                  JOIN trwiki_p.page
                                  ON lawiki_p.page.page_title = trwiki_p.page.page_title 
                                  AND trwiki_p.page.page_namespace = 0
                                  AND trwiki_p.page.page_is_redirect = 0
                                  WHERE lawiki_p.page.page_namespace = 0 AND 
                                  lawiki_p.page.page_is_redirect = 0
                                 )
       THEN CONCAT('[[:la:'||trpage.page_title||'|la]] ')
       WHEN trpage.page_title IN (SELECT ptwiki_p.page.page_title FROM ptwiki_p.page
                                  JOIN trwiki_p.page
                                  ON ptwiki_p.page.page_title = trwiki_p.page.page_title 
                                  AND trwiki_p.page.page_namespace = 0
                                  AND trwiki_p.page.page_is_redirect = 0
                                  WHERE ptwiki_p.page.page_namespace = 0 AND 
                                  ptwiki_p.page.page_is_redirect = 0
                                 )
       THEN CONCAT('[[:pt:'||trpage.page_title||'|pt]] ')
       WHEN trpage.page_title IN (SELECT cawiki_p.page.page_title FROM cawiki_p.page
                                  JOIN trwiki_p.page
                                  ON cawiki_p.page.page_title = trwiki_p.page.page_title 
                                  AND trwiki_p.page.page_namespace = 0
                                  AND trwiki_p.page.page_is_redirect = 0
                                  WHERE cawiki_p.page.page_namespace = 0 AND 
                                  cawiki_p.page.page_is_redirect = 0
                                 )
       THEN CONCAT('[[:ca:'||trpage.page_title||'|ca]] ')
       WHEN trpage.page_title IN (SELECT plwiki_p.page.page_title FROM plwiki_p.page
                                  JOIN trwiki_p.page
                                  ON plwiki_p.page.page_title = trwiki_p.page.page_title 
                                  AND trwiki_p.page.page_namespace = 0
                                  AND trwiki_p.page.page_is_redirect = 0
                                  WHERE plwiki_p.page.page_namespace = 0 AND 
                                  plwiki_p.page.page_is_redirect = 0
                                 )
       THEN CONCAT('[[:pl:'||trpage.page_title||'|pl]] ')
  END) AS suggestions
FROM page AS trpage
/*JOIN enwiki_p.page
ON  trpage.page_title= enwiki_p.page.page_title
AND enwiki_p.page.page_namespace = 0
AND enwiki_p.page.page_is_redirect = 0
LEFT JOIN langlinks
ON ll_from = trpage.page_id*/
LEFT JOIN (SELECT * FROM page_props WHERE pp_propname = 'wikibase_item') AS props
ON pp_page = trpage.page_id
LEFT JOIN revision
ON rev_page = trpage.page_id
WHERE trpage.page_namespace = 0
AND trpage.page_is_redirect = 0
AND rev_parent_id = 0
/*AND ll_title IS NULL*/
AND pp_value IS NULL
AND NOT EXISTS
(SELECT * FROM page_props AS disambig1
WHERE disambig1.pp_propname LIKE 'disambiguation'
AND disambig1.pp_page = trpage.page_id)
GROUP BY trpage.page_title;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
