lvwiki esošie attēli, kas Commons ir ar citu nosaukumu

SQL

use lvwiki_p;
SELECT 
  DISTINCT concat("[[:Attēls:",REPLACE(p.page_title, '_', ' '),"]]") as "Nosaukums lvwiki",
  concat("[[:c:File:",REPLACE(ci.img_name, '_', ' '),"]]") as "Nosaukums Commons"#,
  #il.il_from
FROM image i, commonswiki_p.image ci, categorylinks cl, page p
#left laikam ir lēnāks...
join imagelinks il on il.il_to=p.page_title
#join page p2 on il.il_from=p2.page_title
WHERE i.img_sha1 = ci.img_sha1 and p.page_title != ci.img_name
AND p.page_title = i.img_name
#and p2.page_namespace=10
AND cl.cl_from = p.page_id
AND i.img_sha1 != 'phoiac9h4m842xq45sp7s6u21eteeq1'
and il.il_from in (select page_id from page where page_namespace=10)
order by il.il_from
limit 100;
/* 
v2 (strādā labi)
use lvwiki_p;
SELECT 
#CONCAT('#[[:Attēls:',DISTINCT(lvwiki_p.page.page_title),']]') as "Nosaukums lvwiki",
  DISTINCT(p.page_title) as "Nosaukums lvwiki",
  ci.img_name as "Nosaukums Commons",
  il.il_from
FROM image i, commonswiki_p.image ci, categorylinks cl, page p
#left laikam ir lēnāks...
join imagelinks il on il.il_to=p.page_title
#join page p2 on il.il_from=p2.page_title
WHERE i.img_sha1 = ci.img_sha1 and p.page_title != ci.img_name
AND p.page_title = i.img_name
#and p2.page_namespace=10
AND cl.cl_from = p.page_id
AND i.img_sha1 != 'phoiac9h4m842xq45sp7s6u21eteeq1'
order by il.il_from
limit 500;


veca versija
SELECT
#CONCAT('#[[:Attēls:',DISTINCT(lvwiki_p.page.page_title),']]') as "Nosaukums lvwiki",
  DISTINCT(lvwiki_p.page.page_title) as "Nosaukums lvwiki",
  commonswiki_p.image.img_name as "Nosaukums Commons"
FROM lvwiki_p.image, commonswiki_p.image, lvwiki_p.categorylinks, lvwiki_p.page
WHERE lvwiki_p.image.img_sha1 = commonswiki_p.image.img_sha1 and lvwiki_p.page.page_title != commonswiki_p.image.img_name
AND lvwiki_p.page.page_title = lvwiki_p.image.img_name
AND lvwiki_p.categorylinks.cl_from = lvwiki_p.page.page_id
AND lvwiki_p.image.img_sha1 != 'phoiac9h4m842xq45sp7s6u21eteeq1';
*/
SQL

Checking query status...
