Benutzer:Swiss Energy/Kommunalpolitiker

 CREATE TEMPORARY TABLE temp (ct VARCHAR(255) PRIMARY KEY);
 CREATE TEMPORARY TABLE temp2 (ct VARCHAR(255) PRIMARY KEY);
 INSERT INTO temp(ct)
  VALUES('Kommunalpolitiker_(Schweiz)');
 INSERT INTO temp2 (ct)
  SELECT page_title FROM temp INNER JOIN dewiki_p.categorylinks ON ct=cl_to
   INNER JOIN dewiki_p.page ON cl_from=page_id WHERE page_namespace=14;
 INSERT IGNORE INTO temp (ct)
  SELECT ct FROM temp2;
 INSERT IGNORE INTO temp2 (ct)
  SELECT page_title FROM temp INNER JOIN dewiki_p.categorylinks ON ct=cl_to
   INNER JOIN dewiki_p.page ON cl_from=page_id WHERE page_namespace=14;
 INSERT IGNORE INTO temp (ct)
  SELECT ct FROM temp2;
 INSERT IGNORE INTO temp2 (ct)
  SELECT page_title FROM temp INNER JOIN dewiki_p.categorylinks ON ct=cl_to
   INNER JOIN dewiki_p.page ON cl_from=page_id WHERE page_namespace=14;
 INSERT IGNORE INTO temp (ct)
  SELECT ct FROM temp2;
 INSERT IGNORE INTO temp2 (ct)
  SELECT page_title FROM temp INNER JOIN dewiki_p.categorylinks ON ct=cl_to
   INNER JOIN dewiki_p.page ON cl_from=page_id WHERE page_namespace=14;
 INSERT IGNORE INTO temp (ct)
  SELECT ct FROM temp2;
 INSERT IGNORE INTO temp2 (ct)
  SELECT page_title FROM temp INNER JOIN dewiki_p.categorylinks ON ct=cl_to
   INNER JOIN dewiki_p.page ON cl_from=page_id WHERE page_namespace=14;
 INSERT IGNORE INTO temp (ct)
  SELECT ct FROM temp2;
 SELECT CONCAT('# [[:',REPLACE(page_title,'_',' '),']]')
  FROM dewiki_p.categorylinks cl1 INNER JOIN dewiki_p.pagelinks pl1 ON pl_from=cl_from 
   INNER JOIN dewiki_p.page ON cl_from=page_id
  WHERE cl_to IN ('Mann','Frau','Intersexueller') AND pl_namespace=0 AND pl_title IN ('Gemeinderat_(Schweiz)','Einwohnerrat') AND page_namespace=0
   AND cl_from NOT IN (SELECT cl_from FROM dewiki_p.categorylinks INNER JOIN temp ON cl_to=ct)
   ORDER BY page_title;
 SELECT CONCAT('* Einträge in [[:Kategorie:Kommunalpolitiker (Schweiz)]]: ', COUNT(DISTINCT cl_from))
  FROM dewiki_p.categorylinks INNER JOIN temp ON cl_to=ct INNER JOIN dewiki_p.page ON cl_from=page_id
  WHERE page_namespace=0;
 SELECT CONCAT('* Personenartikel mit Link auf [[Gemeinderat (Schweiz)]] und/oder [[Einwohnerrat]]: ', COUNT(DISTINCT cl_from))
  FROM dewiki_p.categorylinks cl1 INNER JOIN dewiki_p.pagelinks pl1 ON pl_from=cl_from 
   INNER JOIN dewiki_p.page ON cl_from=page_id
  WHERE cl_to IN ('Mann','Frau','Intersexueller') AND pl_namespace=0 AND pl_title IN ('Gemeinderat_(Schweiz)','Einwohnerrat') AND page_namespace=0;