Personen mit Klammerlemma und einem Staat in der Klammer

This query is marked as a draft This query has been published by Wurgl.

Siehe dazu
https://de.wikipedia.org/w/index.php?title=Benutzer_Diskussion:Wurgl&oldid=211301841

SQL

/* der einfache Fall mit adjektiv */
SELECT DISTINCT CONCAT('[[', REPLACE(page_title, '_', ' '), ']]') AS "Artikel"
  FROM page, templatelinks
 WHERE page_namespace = 0
   AND page_title LIKE "%\\_(%)"
       /* ische bzw. ischer gefolgt von Leerzeichen (das wird in der Datenbank als Unterstreichung genommen */
   AND (
        page_title RLIKE "\\(.*ischer?_.*\\)"
     OR page_title RLIKE "\\(.*deutscher?_.*\\)"
     OR page_title RLIKE "\\(.*yscher?_.*\\)"
       )
   AND page_title NOT RLIKE "\\(Nordischer_Kombinierer(,_[0-9]*)?\\)" /* Nordischer Kombinierer, (Jahreszahl) rausnehemen */
   AND page_title NOT RLIKE "\\(.*atholischer_.*\\)" /* Katholischer rausnehmen */
   AND page_title NOT RLIKE "\\(.*uddhistischer_.*\\)" /* buddhistischer bzw. Buddhistischer rausnehmen */
   AND tl_from = page_id
   AND tl_namespace = 10
   AND tl_title = 'Personendaten'
 ORDER BY page_title;

/* zweite Query: In der Klammer ist ein Staat */
WITH RECURSIVE Cat AS (SELECT page_title
                        FROM page
                       WHERE page_title = 'Staat'
                         AND page_namespace = 14
                       UNION
                      SELECT SubCat.page_title
                        FROM page AS SubCat, categorylinks, Cat
                       WHERE SubCat.page_namespace = 14
                         AND cl_from = SubCat.page_id
                         AND cl_to = Cat.page_title
                         AND cl_type = "subcat"
                     )
SELECT DISTINCT CONCAT('[[', REPLACE(Person.page_title, '_', ' '), ']]') AS "Artikel"
  FROM Cat, categorylinks, page AS Staat, page AS Person, templatelinks
 WHERE cl_to = Cat.page_title
   AND Staat.page_id = cl_from
   AND Staat.page_namespace = 0
   AND cl_type = "page"
   AND Person.page_namespace = 0
   AND Person.page_title LIKE "%\\_(%)"
   AND Person.page_title RLIKE CONCAT("[(_]", Staat.page_title, "[,)]")
   AND tl_from = Person.page_id
   AND tl_namespace = 10
   AND tl_title = 'Personendaten'
 ORDER BY Person.page_title;

/* dritte Query: In der Klammer ist eine Weiterleitung auf einen Staat (z.B. USA) */
WITH RECURSIVE Cat AS (SELECT page_title
                        FROM page
                       WHERE page_title = 'Staat'
                         AND page_namespace = 14
                       UNION
                      SELECT SubCat.page_title
                        FROM page AS SubCat, categorylinks, Cat
                       WHERE SubCat.page_namespace = 14
                         AND cl_from = SubCat.page_id
                         AND cl_to = Cat.page_title
                         AND cl_type = "subcat"
                     )
SELECT DISTINCT CONCAT('[[', REPLACE(Person.page_title, '_', ' '), ']]') AS "Artikel"
  FROM Cat, categorylinks, page AS Staat, redirect, page AS RedirStaat, page AS Person, templatelinks
 WHERE cl_to = Cat.page_title
   AND Staat.page_id = cl_from
   AND Staat.page_namespace = 0
   AND cl_type = "page"
   AND RedirStaat.page_is_redirect = 1
   AND rd_from = RedirStaat.page_id
   AND rd_title = Staat.page_title
   AND rd_namespace = 0
   AND Person.page_namespace = 0
   AND Person.page_title LIKE "%\\_(%)"
   AND Person.page_title RLIKE CONCAT("[(_]", RedirStaat.page_title, "[,)]")
   AND tl_from = Person.page_id
   AND tl_namespace = 10
   AND tl_title = 'Personendaten'
 ORDER BY Person.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...